SUMIFS

Naresh Patel
Naresh Patel used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
helpfinderIT Consultant

Commented:
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
Cost Accountant
Top Expert 2012
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
Naresh PatelFinancial Adviser

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Naresh PatelFinancial Adviser

Author

Commented:
& if possible how do I get same results by using SUMIFS..?
Most Valuable Expert 2011
Top Expert 2011

Commented:
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
SteveCost Accountant
Top Expert 2012

Commented:
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?
Naresh PatelFinancial Adviser

Author

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.
Naresh PatelFinancial Adviser

Author

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
Most Valuable Expert 2011
Top Expert 2011

Commented:
& if possible how do I get same results by using SUMIFS..?

You asked, so I answered. ;)
Naresh PatelFinancial Adviser

Author

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
Most Valuable Expert 2011
Top Expert 2011
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")))
Naresh PatelFinancial Adviser

Author

Commented:
@Rory Archiblad.

Perfect !!!!

Thanks
Naresh PatelFinancial Adviser

Author

Commented:
Awesome thanks all of you.

@Steve .....one request what is "N" Stands for in formula.
Naresh PatelFinancial Adviser

Author

Commented:
any guess for this Formula Mis

Thanks
SteveCost Accountant
Top Expert 2012

Commented:
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.
Naresh PatelFinancial Adviser

Author

Commented:
Thank You Sir

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial