Naresh Patel asked on # SUMIFS

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

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

Microsoft ExcelMicrosoft OfficeOffice Suites-Other

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a questionNaresh Patel

@helpfinder

Thanks

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

Naresh Patel

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Rory Archibald

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

=TEXT(A2,"mmm")

then you can use:

=SUMIFS($D:$D,$C:$C,$F2,$B

See attached. I also added a pivot table version which will create the whole table for you.

Steve

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?

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 Patel

@Rory Archibald

Thank you very much all of you.

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Naresh Patel

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

i.e. =SUMPRODUCT($C$2:$C$17,

Thanks

Rory Archibald

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

You asked, so I answered. ;)

Naresh Patel

its my bad @Rory Archibald....but expecting same result using SUMIFS without any change in WB. I have to mention that also ....apology.

Thanks

Thanks

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Naresh Patel

@Rory Archiblad.

Perfect !!!!

Thanks

Perfect !!!!

Thanks

Naresh Patel

Awesome thanks all of you.

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

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

Naresh Patel

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Steve

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.

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 Patel

Thank You Sir

=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