# 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)

Regards
SUDARSAN

#### Attachments

• 20.5 KB Views: 10
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
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

REGARDS
SUDARSAN

#### Attachments

• 20.9 KB Views: 5

#### 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

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

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

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.

Regards

Last edited:

#### SUDARSAN

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

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

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.

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)

regards
Sudarsan

#### Attachments

• 64.6 KB Views: 0

#### 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

• 64.6 KB Views: 7

#### 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

You could use table with Your data.

Last edited: