Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SUMIFS

Posted on 2014-03-02
16
Medium Priority
?
328 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:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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:Naresh Patel
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Author Comment

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

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:Naresh Patel
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 1000 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:Naresh Patel
ID: 39899907
@Rory Archiblad.

Perfect !!!!

Thanks
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39899910
Awesome thanks all of you.

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

Author Comment

by:Naresh Patel
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:Naresh Patel
ID: 39899940
Thank You Sir
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

722 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