SUM based on same value across multiple columns

[creative999]

Hi

I have a formula SUMIF formula which works but now need to update it so part of the formula looks for the same value across two columns.

The formula needs to look at column B OR column D for Z1 value.

=SUM(SUMIFS(SHEET1!$A$2:$A$200,SHEET1!$B$2:$B$200,Z1, SHEET1!$C$2:$C$200,"9999"))

 

[awoohaw]

Try a SUPRODUCT array formula. If in 2019 you may need to enter the formula with the CNTL-SHFT-ENTR keystroke combination
I took the sheet references out, you don't need them if you are on the same worksheet.

=SUMPRODUCT($A$2:$A$200 *
(($B$2:$B$200=Z1)+($D$2:$D$200=Z1))
*(($C$2:$C$200)="9999"))

..

 

[creative999]

Try a SUPRODUCT array formula. If in 2019 you may need to enter the formula with the CNTL-SHFT-ENTR keystroke combination
I took the sheet references out, you don't need them if you are on the same worksheet.

=SUMPRODUCT($A$2:$A$200 *
(($B$2:$B$200=Z1)+($D$2:$D$200=Z1))
*(($C$2:$C$200)="9999"))

..

Thank you...
Tried the formula where C doesnt contain 9999 but getting a £0 value.
I also need to add some additional conditions... ie, E="ABC", F greater or equal to "100"

 

[awoohaw]

Thank you...
Tried the formula where C doesnt contain 9999 but getting a £0 value.
I also need to add some additional conditions... ie, E="ABC", F greater or equal to "100"

Would be be kind enough to post some data, so I can build a formula with a worksheet instead of guessing what your data looks like?
an expected result or two would be helpful.

the xl2bb add in is the best way to post a mini sheet (it will post formulas and the calculations), if you cannot use that then please post a table which can be copied by the forum (images are not copyable, and mean the forum must recreate the scenario in total)

And you should get a zero value if there is no "9999", that is what is calculated when C2:C200 = "9999" will give you on row 50, if C50 is not equal to "9999".
In SUMPRODUCT array formulas, asterisks are "ANDS" and pluses are "ORS".
just curious, is column C really text?