SUMIFS and SUMPRODUCT using the SUM of Multiple Columns

[quiqueperez]

Hi,

I need to write a formula to sum the values in column A based on a number of conditions, including one that would be the sum of the values in a number of columns.

Using the following example, I would need to add the values in column A if condition 1 = Australia and the sum of the values in ITEM 1, 2 and 3 is greater than 0.

In the current example the result woud be 55 (row3 + row4)


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]VALUES TO ADD[/TD]
[TD]CONDITION 1[/TD]
[TD]ITEM 1[/TD]
[TD]ITEM 2[/TD]
[TD]ITEM 3[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]US[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]AUSTRALIA[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]AUSTRALIA[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]AUSTRALIA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]



The formula I'm thinking of is something like this, but I don't know how to formulate it,

SUMIFS(VALUES TO ADD, CONDITION 1, AUSTRALIA, SUM(AREA 1, AREA 2, AREA 3), ">0")


Similarly, I also need a variant of the above to do a SUMPRODUCT of column A times the sum of ITEM 1 + 2+ 3.
Something like,

SUMPRODUCT(VALUES TO ADD, SUM(ITEM1, ITEM2, ITEM3), CONDITION1="AUSTRALIA")



Your help would be very much appreciated.

Cheers,
Enrique

 

[FDibbins]

This will give you the sum....
=SUMPRODUCT((A2:A5)*(B2:B5="Australia")*(SUM(C2:E5)>0))

For the total...
=SUMPRODUCT((A2:A5)*(B2:B5="Australia")*(C2:E5))

 

[Marcelo Branco]

Maybe

=SUMPRODUCT(--(B2:B5="Australia"),--(SUBTOTAL(9,OFFSET(C2:E5,ROW(C2:E5)-ROW(C2),0,1,3))>0),A2:A5)

Hope this helps

M.

 

[quiqueperez]

Hi FDibbins,

Your formula is really clean and simple, but I think in the fist case is summing the whole range C2 to E5 and then evaluating the condition >0. Instead of evaluating the condition row by row (SUM(C2:E2)>0?, SUM(C3:E3)>0?, etc).

Is there a simple fix for this?

 

[Marcelo Branco]

Have you tried my formula in #3?

M.

 

[quiqueperez]

Hi Marcelo,
I'm testing your formula but it's giving me some errors...
For some reason seems to be missing some values.
Also, would it work with blank cells?, if I set a a larger range so I dont have to amend it every time.
Thanks,
Enrique

 

[Marcelo Branco]

See if this is ok

Type in G2 the country of interes
The formula sums the rows in red


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

VALUES TO ADD​

[/td][td]

CONDITION 1​

[/td][td]

ITEM 1​

[/td][td]

ITEM 2​

[/td][td]

ITEM 3​

[/td][td] [/td][td]

Country​

[/td][td]

Result​

[/td][/tr]


[tr][td]

2​

[/td][td]

56​

[/td][td]

US​

[/td][td]

1​

[/td][td]

0​

[/td][td]

2​

[/td][td] [/td][td]

Australia​

[/td][td]

105​

[/td][/tr]


[tr][td]

3​

[/td][td]

23​

[/td][td]

AUSTRALIA​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

4​

[/td][td]

32​

[/td][td]

AUSTRALIA​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

5​

[/td][td]

14​

[/td][td]

AUSTRALIA​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

6​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

7​

[/td][td]

34​

[/td][td]

US​

[/td][td]

0​

[/td][td]

2​

[/td][td]

1​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

8​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

9​

[/td][td]

50​

[/td][td]

AUSTRALIA​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

10​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

11​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

12​

[/td][td]

40​

[/td][td]

AUSTRALIA​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

13​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

14​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in F2
=SUMPRODUCT(--(B2:B100=G2),--(SUBTOTAL(9,OFFSET(C2:E100,ROW(C2:E100)-ROW(C2),0,1,3))>0),A2:A100)

M.