Sumifs with or condition over multiple columns

[quiqueperez]

Hi,

I'm struggling to write a formula to sum the values in column A based on a number of conditions, including one that applies over multiple columns.

Using the following example, I would need to add the values in column A if condition 1 = Australia and the value in at least one of the areas is greater than zero.

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

VALUES TO ADD	CONDITION 1	AREA 1	AREA 2	AREA 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")

Any would be very much appreciated.

Cheers,
Enrique

 

[Marcelo Branco]

Duplicate

http://www.mrexcel.com/forum/excel-...fs-sumproduct-using-sum-multiple-columns.html

M.

 

[Domenic]

That's great, glad you have it sorted out...

 

[quiqueperez]

Hi,
I'm having another problem with the above formula.
If I set in the forumual a larger range than the actual range in the source spreadsheet, the formual will return an error.
Since I'm going to be re-linking the formula to different reports, I need the flexibility to cover chnaging cell ranges.
Thanks,
Enrique

 

[Aladin Akyurek]

Hi,
I'm having another problem with the above formula.
If I set in the forumual a larger range than the actual range in the source spreadsheet, the formual will return an error.
Since I'm going to be re-linking the formula to different reports, I need the flexibility to cover chnaging cell ranges.
Thanks,
Enrique

What is the error that you get?

 

[quiqueperez]

Hi Aladin,
Since I'm going to use the formula in a template spreadsheet, I'm seting a cells range larger than the actual range in the current data source.
So, if I'm expecting my data source to have a few hundred lines, I want to enter in the formula a range of upto row 1000 for example.
But, when I do this, the formulas return a #VALUE! because they can't evaluate cells are not numbers (blanks, text, ext return the same error)

 

[Marcelo Branco]

quiqueperez, Domenic and Aladin


There are two threads with the same issue - it's becoming a mess

I'll reply just to this thread - this is my suggestion

	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​

<tbody>
</tbody>

Type the country of interest in G2

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

M.

 

[Aladin Akyurek]

Hi Aladin,
Since I'm going to use the formula in a template spreadsheet, I'm seting a cells range larger than the actual range in the current data source.
So, if I'm expecting my data source to have a few hundred lines, I want to enter in the formula a range of upto row 1000 for example.
But, when I do this, the formulas return a #VALUE! because they can't evaluate cells are not numbers (blanks, text, ext return the same error)

The current range the formula addresses is A2:C5. On which is this range?

 

[quiqueperez]

Hi Aladin,
The current real data source range is Q2:Y385, but that can vary.
So, I wanted to set the formula to something like Q2:Y1000 so when a new report is available I just need to update the external links without having to touch the formulas.

 

[Aladin Akyurek]

Hi Aladin,
The current real data source range is Q2:Y385, but that can vary.
So, I wanted to set the formula to something like Q2:Y1000 so when a new report is available I just need to update the external links without having to touch the formulas.

You forgat the name of the sheet. Let's assume Sheet1.

Column Q houses the values to add.
Column R houses the countries (condition).
Columns S to Y houses the areas.

1. Static set up...

a) Using Marcelo's suggestion, just enter:

=SUMPRODUCT(
  --($R$2:$R$1000="AUSTRALIA"),
  --(SUBTOTAL(9,OFFSET($S$2,ROW($S$2:$Y$1000)-ROW($S$2),0,1,COLUMNS($S$2:$Y$1000)))>0),
  $Q$2:$Q$1000)

b) Control+shift+enter, not just enter:

=SUM(IF(MMULT(IF(ISNUMBER($S$2:$Y$1000),$S$2:$Y$1000,0),
  TRANSPOSE(COLUMN($S$2:$Y$1000)^0))>0,IF($R$2:$R$1000="AUSTRALIA",
  $Q$2:$Q$1000)))

2. Dynamic set up (w.r.t. vertical changes)...

Define Lrow using Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$Q:$Q)

Define Values2Add as referring to:

=Sheet1!$Q$2:INDEX(Sheet1!$Q:$Q,Lrow)

Define Countries as referring to:

=Sheet1!$R$2:INDEX(Sheet1!$R:$R,Lrow)

Define Areas as referring to:

=Sheet1!$S$2:INDEX(Sheet1!$Y:$Y,Lrow)

a) just enter:

=SUMPRODUCT(
  --(Countries="AUSTRALIA"),
  --(SUBTOTAL(9,OFFSET(INDEX(Areas,1,1),ROW(Areas)-ROW($S$2),0,1,COLUMNS(Areas)))>0),
  Values2Add)

b) Control+shift+enter, not just enter:

=SUM(IF(MMULT(IF(ISNUMBER(Areas),Areas,0),
  TRANSPOSE(COLUMN(Areas)^0))>0,IF(Countries="AUSTRALIA",
  Values2Add)))