SUMIFS with multiple column criteria range

[walkwithred]

Hello,

I'm trying to write a SUMIFS formula where the range for the first piece of criteria spans multiple columns as shown in the example formula below:

=SUMIFS(Sheet2!N:N,Sheet2!O:Z,Sheet1!A3)

So the value in A3 on Sheet1 can appear in any column between O and Z in Sheet2 over multiple rows. In addition, the column it appears in can be different for each row.

Have not had any luck getting this to work and would appreciate any suggestions.

Best,

Red

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(Sheet2!O2:Z400=Sheet1!A3,Sheet2!N2:N400))

This will pick out every occurrence of the condition in O2:Z400 on Sheet2.

 

[walkwithred]

Thank you, Aladin.

Is it possible to use AND OR functions in this array as I do need to add additional (single column) criteria, hence the SUMIFS function?

 

[Aladin Akyurek]

Thank you, Aladin.

Is it possible to use AND OR functions in this array as I do need to add additional (single column) criteria, hence the SUMIFS function?

Yes, that's possible. Example:

=SUM(IF(Sheet2!K2:K400=Sheet1!B3,IF(Sheet2!O2:Z400=Sheet1!A3,Sheet2!N2:N400)))

Note. Trouble is that you can't invoke SumIfs when the terms are not of the the same dimension like multiple columns vs single column.

 

[walkwithred]

I've experimented and it seems that the array will not work with multiple conditions (at least as far as I can figure out).

I managed to get the desired result by creating an additional column with the values in columns O through Z concatenated (=O2&" "&P2&" "&etc.) and then using wildcards in the SUMIFS function:

=SUMIFS(Sheet2!N:N,Sheet2!O:O,"*"&Sheet1!A3&"*",Sheet2!G:G,"Condition 2")

Hopefully this will help others in a similiar predicament.

Thanks!

 

[Aladin Akyurek]

I've experimented and it seems that the array will not work with multiple conditions (at least as far as I can figure out).

I managed to get the desired result by creating an additional column with the values in columns O through Z concatenated (=O2&" "&P2&" "&etc.) and then using wildcards in the SUMIFS function:

=SUMIFS(Sheet2!N:N,Sheet2!O:O,"*"&Sheet1!A3&"*",Sheet2!G:G,"Condition 2")

Hopefully this will help others in a similiar predicament.

Thanks!

Concatanation is ok. However, your judgement regarding array formulas is not:

=SUM(IF(Sheet2!G2:G400="Condition 2",IF(Sheet2!O2:Z400=Sheet1!A3,Sheet2!N2:N400)))

 

[walkwithred]

Cheers, Aladin. We posted at the same time.

I see. A nested IF would not have occured to me, such is my limited knowledge of arrays.

Appreciate the time and help

 

[Aladin Akyurek]

Cheers, Aladin. We posted at the same time.

I see. A nested IF would not have occured to me, such is my limited knowledge of arrays.

Appreciate the time and help

No problem. You're welcome. A small note involving concatenation:

=A2&"|"&B2

would be better than just

=A2&B2

for 1xy2 does not diffrerentiate 1x and y2 on one hand; 1 and xy2 on the other.