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)

VALUES TO ADD	CONDITION 1	ITEM 1	ITEM 2	ITEM 3
56	US	1	0	2
23	AUSTRALIA	0	1	0
32	AUSTRALIA	1	1	2
14	AUSTRALIA	0	0	0

<tbody>
</tbody>

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

	A​	B​	C​	D​	E​	F​	G​	H​
1​	VALUES TO ADD​	CONDITION 1​	ITEM 1​	ITEM 2​	ITEM 3​		Country​	Result​
2​	56​	US​	1​	0​	2​		Australia​	105​
3​	23​	AUSTRALIA​	0​	1​	0​
4​	32​	AUSTRALIA​	1​	1​	2​
5​	14​	AUSTRALIA​	0​	0​	0​
6​
7​	34​	US​	0​	2​	1​
8​
9​	50​	AUSTRALIA​	1​	0​	0​
10​
11​
12​	40​	AUSTRALIA​	0​	0​	0​
13​
14​

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

M.