Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Hi Experts,

This is regarding sumifs formula want to calculate total amount for particular month.

I am want to sum the amount for particular Name for particular month. real trick is - want to match month which is like "Jan Feb Mar".

see attached file

Thanks

SUMIFS.xlsx

This is regarding sumifs formula want to calculate total amount for particular month.

I am want to sum the amount for particular Name for particular month. real trick is - want to match month which is like "Jan Feb Mar".

see attached file

Thanks

SUMIFS.xlsx

where 1 is representing 1st month (January), so for Feb change to 2, MArch 3 etc.

Maybe you will need to replace semicolons (;) in my formula with commas (,) - it depends on your regional settings

SUMIFS.xlsx

I guess you misinterpret my question.@Steve

Yes Perfect. will you pls explain how it works as I search for whole one day but I dint find.

Thanks

=TEXT(A2,"mmm")

then you can use:

=SUMIFS($D:$D,$C:$C,$F2,$B

See attached. I also added a pivot table version which will create the whole table for you.

SUMIFS-2-.xlsx

As for the SUMPRODUCT formula it works by handling the data as an array...

Sumproduct multiplies the results of a row of array data... then sums the rows...

So for data:

20 | 0 | 1 | 1

30 | 1 | 1 | 0

40 | 1 | 1 | 1

50 | 1 | 1 | 1

20x0x1x1 = 0

30x1x1x0 = 0

40x1x1x1 = 40

50x1x1x1 = 50

so sumproduct = 90.

In your data the 1 and 0 are gained as the result of the boolean true false result of the formula converted using N() ... N(turns true false into 1 or 0)

This is how SUMPRODUCT works like SUMIFS with a bit more flexibility.

Does this make some sense or would you like further info?

I had already this kind of thought in mind but I don't want to apply that way. as I got answer perfect from Sir.Steve "The_Barman"....so this question is solved I am just waiting for explanation about solution.

Thank you very much all of you.

i.e. =SUMPRODUCT($C$2:$C$17,

Thanks

Thanks

=SUMIFS($C:$C,$B:$B,$E2,$A

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=SUMPRODUCT($C$2:$C$17,N(TEXT($A$2:$A$17,"mmm")=F$1),N($B$2:$B$17=$E2)) Sumproduct.xlsx