• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to use sumif index, match effectively

SUDARSAN

Member
Dear Forum Friends,

I have tried below formula but it is not working what I need to get , may be I may doing mistake some where , please correct any one
=SUMIF($S$1:$DV$1,"<="&$C2,INDEX($S$2:$DV$324,MATCH($A2,$R$2:$R$324,0),0))

it should sum up with in the particular months only

like Oct18 to Sep19 only , not total row or previous (Nov17- Sep 18) or next (oct19-sep-20)

sheet attached for your reference

please help

Thank you in advance
Regards
SUDARSAN
 

Attachments

Last edited by a moderator:

vletm

Excel Ninja
SUDARSAN
Syntax: =SUMIF(range, criteria, [sum_range])
You've asked to sum values which are smaller or equal than one date = always all months from Dec-13.
If You would like to sum eg single month then there should be two criterias ( from - to ) with SUMIFS-function.
 

SUDARSAN

Member
To me tour explanation is obscure. Please add some manually calculated results to your sheet. Thanks
Thank you ,

here is I added manual calculation (sheet also attached )

for example A6(939) TOTAL IS FROM "AUGUST 2014 TO JULY 2015" IS "0" BUT IT SHOWING TOTAL SUM "7354" FROM STARTING AUG 2014 TO MARCH 2021

AND A2(804) TOO

NEED TO SUM ONLY GIVEN MONTHS

PLEASE HELP

THANK YOU IN ADVANCE

REGARDS
SUDARSAN
 

Attachments

SUDARSAN

Member
THA
SUDARSAN
Syntax: =SUMIF(range, criteria, [sum_range])
You've asked to sum values which are smaller or equal than one date = always all months from Dec-13.
If You would like to sum eg single month then there should be two criterias ( from - to ) with SUMIFS-function.
THANK YOU
IF POSSIBLE CAN YOU PLEASE PUT A FORMULA
 

vletm

Excel Ninja
SUDARSAN
As written in Forum Rules which You should reread
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • =SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")
  • =SUMIFS(A2:A9,B2:B9,"<>Bananas",C2:C9,"Tom")
 
Last edited:

SUDARSAN

Member
SUDARSAN
As written in Forum Rules which You should reread
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • =SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")
  • =SUMIFS(A2:A9,B2:B9,"<>Bananas",C2:C9,"Tom")
Thank you
 

SUDARSAN

Member
SUDARSAN
As written in Forum Rules which You should reread
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • =SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")
  • =SUMIFS(A2:A9,B2:B9,"<>Bananas",C2:C9,"Tom")
Thank you Mr Vletm,

thank you very much for your valuable time and Patience ,

I am sorry

I have not understood how to match the sl no and sum those particular months

regards
SUDARSAN
 

bosco_yip

Excel Ninja
Thank you ,

here is I added manual calculation (sheet also attached )

for example A6(939) TOTAL IS FROM "AUGUST 2014 TO JULY 2015" IS "0" BUT IT SHOWING TOTAL SUM "7354" FROM STARTING AUG 2014 TO MARCH 2021

AND A2(804) TOO

NEED TO SUM ONLY GIVEN MONTHS

PLEASE HELP

THANK YOU IN ADVANCE

REGARDS
SUDARSAN
There is no solution to your question based on your attached file.

Please take notice to your D6 formula :

=SUMIF($S$1:$DV$1,"<="&$C6,INDEX($S$2:$DV$324,MATCH($A6,$R$2:$R$324,0),0))

Your criteria C6 =1/8/2014

But, your SUMIF formula range S1:DV1 =1/12/1913 to 1/11/1922

There is 100 years difference !!

So, your Lookup Table cannot be used in this moment.

Please make revision to the Lookup table header year period and re-loading the file in accordingly

Regards
 
Last edited:

SUDARSAN

Member
There is no solution to your question based on your attached file.

Please take notice to your D6 formula :

=SUMIF($S$1:$DV$1,"<="&$C6,INDEX($S$2:$DV$324,MATCH($A6,$R$2:$R$324,0),0))

Your criteria C6 =1/8/2014

But, your SUMIF formula range S1:DV1 =1/12/1913 to 1/11/1922

There is 100 years difference !!

So, your Lookup Table cannot be used in this moment.

Please make revision to the Lookup table header year period and re-loading the file in accordingly

Regards
Thank you very much for your time,

I am sorry ,

now I corrected it , Kindly go through and I have tried based on Mr vletm given formula as below but it also not working

=SUMIFS($D$33:$D$2593,$C$33:$C$2593,">="&F34,$C$33:$C$2593,"<="&H34,B33:B2624,A34)

please help
regards
Sudarsan
 

Attachments

SUDARSAN

Member
SUDARSAN
As written in Forum Rules which You should reread
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • =SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")
  • =SUMIFS(A2:A9,B2:B9,"<>Bananas",C2:C9,"Tom")
Thank you mr Vletm,
I tried like
=SUMIFS($D$33:$D$2593,$C$33:$C$2593,">="&F34,$C$33:$C$2593,"<="&H34,B33:B2624,A34)
but getting error

regards
Sudarsan
 

Attachments

vletm

Excel Ninja
SUDARSAN
#1 You try to sum eg cell B33 ( = SI. NO ) ...
#2 Your ranges vary ... modify all ranges (rows) same
... after those results will be as below
Screenshot 2021-04-28 at 09.21.59.png
You could use table with Your data.
 
Last edited:
Top