Solved

# SUMIFS

Posted on 2014-03-02
312 Views
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
0
Question by:itjockey
• 9
• 3
• 3
• +1

LVL 19

Expert Comment

ID: 39899705
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

LVL 24

Accepted Solution

Steve earned 250 total points
ID: 39899711
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

LVL 8

Author Comment

ID: 39899776
@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

LVL 8

Author Comment

ID: 39899780
& if possible how do I get same results by using SUMIFS..?
0

LVL 85

Expert Comment

ID: 39899833
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

LVL 24

Expert Comment

ID: 39899843
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

LVL 8

Author Comment

ID: 39899849
@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

LVL 8

Author Comment

ID: 39899857
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

LVL 85

Expert Comment

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

0

LVL 8

Author Comment

ID: 39899885
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

LVL 85

Assisted Solution

Rory Archibald earned 250 total points
ID: 39899893
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

LVL 8

Author Comment

ID: 39899907

Perfect !!!!

Thanks
0

LVL 8

Author Closing Comment

ID: 39899910
Awesome thanks all of you.

@Steve .....one request what is "N" Stands for in formula.
0

LVL 8

Author Comment

ID: 39899926
any guess for this Formula Mis

Thanks
0

LVL 24

Expert Comment

ID: 39899935
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

LVL 8

Author Comment

ID: 39899940
Thank You Sir
0

## Featured Post

### Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.