SUMIFS

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
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
Is the attached what you are looking to do?

=SUMPRODUCT($C$2:$C$17,N(TEXT($A$2:$A$17,"mmm")=F$1),N($B$2:$B$17=$E2))
Sumproduct.xlsx
0
 
helpfinderIT ConsultantCommented:
try this formula
=SUMPRODUCT((MONTH(A2:A17)=1)*(C2:C17))
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
0
 
Naresh PatelTraderAuthor Commented:
@helpfinder
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
0
Get expert help—faster!

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

 
Naresh PatelTraderAuthor Commented:
& if possible how do I get same results by using SUMIFS..?
0
 
Rory ArchibaldCommented:
I'd suggest a helper column (I inserted it as a new column B) to create the formatted months for you using:
=TEXT(A2,"mmm")
then you can use:

=SUMIFS($D:$D,$C:$C,$F2,$B:$B,G$1)

See attached. I also added a pivot table version which will create the whole table for you.
SUMIFS-2-.xlsx
0
 
SteveCommented:
SUMIFS will not allow for the use of TEXT in the function so you would require the helper column. (or use of a range of dates).

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?
0
 
Naresh PatelTraderAuthor Commented:
@Rory Archibald
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.
0
 
Naresh PatelTraderAuthor Commented:
I just want to know what "N" stands for in formula?

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



Thanks
0
 
Rory ArchibaldCommented:
& if possible how do I get same results by using SUMIFS..?

You asked, so I answered. ;)
0
 
Naresh PatelTraderAuthor Commented:
its my bad @Rory Archibald....but expecting same result using SUMIFS without any change in WB. I have to mention that also ....apology.



Thanks
0
 
Rory ArchibaldConnect With a Mentor Commented:
You'd have to build up month strings then use a conjunction of > 1st of month and 1st of next month, so something like:

=SUMIFS($C:$C,$B:$B,$E2,$A:$A,">="&DATEVALUE("01-"&F$1&"-2014"),$A:$A,"<"&IF(G$1="",DATE(2015,1,1),DATEVALUE("01-"&G$1&"-2014")))
0
 
Naresh PatelTraderAuthor Commented:
@Rory Archiblad.

Perfect !!!!

Thanks
0
 
Naresh PatelTraderAuthor Commented:
Awesome thanks all of you.

@Steve .....one request what is "N" Stands for in formula.
0
 
Naresh PatelTraderAuthor Commented:
any guess for this Formula Mis

Thanks
0
 
SteveCommented:
N is a function in the same way as SUM of IF.

N converts values to numbers (similar to using -- or *1)
N in this case converts the booleans from True / False to 1 or 0

N(False) = 0
N(True) = 1

The excel help has more on the function.
0
 
Naresh PatelTraderAuthor Commented:
Thank You Sir
0
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.