• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs for Multiple Columns

S

srikandi2000

Member
[SOLVED]
Dear All,

I have been trying to modify Narayank991 solution to my previous problem to make -sumifs- sums for more than one columns to no avail. Can anyone propose a solution for this?

Thanks in advance.
 

Attachments

  • example for chandoo.org.xlsx
    9.4 KB · Views: 85
Hi ,

Do you mean to say that instead of summing one column ( column C ) , you now want to sum both column C and column D ?

Narayan
 
Hi Srikandi,

Do you want to have both columns being summed up? Or an option to select either column C or D?

If the first, can't you use a subtotal column of number of teachers and base your formula on that column?
 
I want to have both columns being summed up with just one formula (without using sumifs() + sumifs())

EDIT: adding subtotal columns is a nice solution, but is it possible to use a formula?
 
Hi ,

A search on the internet says it's not possible ; instead of SUMIF or SUMIFS , use SUM(IF(...)) entered as an array formula.

In your case , the formula would be :

=SUM(IF($A$2:$A$12=IF($H$1="All",$A$2:$A$12,$H$1),IF($B$2:$B$12=IF($H$2="All",$B$2:$B$12,$H$2),$C$2:$D$12)))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan
 
My gosh.... I've been trying to do that for an hour already... never crosses my mind to add the IF directly in an IF logical.

Thanks a lot Narayank991..
 
Ah, Narayan is again quicker then me :)

Nevertheless, here is mine:
Code: 
=SUMPRODUCT(($A$2:$A$12=IF($H$1="All",$A$2:$A$12,$H$1))*($B$2:$B$12=IF($H$2="All",$B$2:$B$12,$H$2)),$C$2:$C$12+$D$2:$D$12)
No use for array entering ;)
 
You must log in or register to reply here.
Back
Top