Solved

SUMIFS

Posted on 2014-03-02
16
312 Views
Last Modified: 2014-03-03
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
Comment
Question by:itjockey
  • 9
  • 3
  • 3
  • +1
16 Comments
 
LVL 19

Expert Comment

by:helpfinder
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

by:
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

by:itjockey
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

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

Expert Comment

by:Rory Archibald
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

by:Steve
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

by:itjockey
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

by:itjockey
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 85

Expert Comment

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

You asked, so I answered. ;)
0
 
LVL 8

Author Comment

by:itjockey
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

by:Rory Archibald
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

by:itjockey
ID: 39899907
@Rory Archiblad.

Perfect !!!!

Thanks
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39899910
Awesome thanks all of you.

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

Author Comment

by:itjockey
ID: 39899926
any guess for this Formula Mis

Thanks
0
 
LVL 24

Expert Comment

by:Steve
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

by:itjockey
ID: 39899940
Thank You Sir
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now