Solved

SUMIFS

Posted on 2014-03-02
16
317 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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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