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

ASKER CERTIFIED SOLUTION

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

@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

ASKER

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

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.

SUMIFS-2-.xlsx

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?

ASKER

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

ASKER

I just want to know what "N" stands for in formula?

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

Thanks

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

**(TEXT($A$2:$A$17,"mmm")=F$**__N__**($B$2:$B$17=$E2))**

__N__Thanks

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

You asked, so I answered. ;)

ASKER

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

SOLUTION

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

@Rory Archiblad.

Perfect !!!!

Thanks

Perfect !!!!

Thanks

ASKER

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.

ASKER

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.

ASKER

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

SUMIFS.xlsx