ExcelBanter - Sumif where sum range is more than one column?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Sumif where sum range is more than one column? (https://www.excelbanter.com/excel-worksheet-functions/32064-sumif-where-sum-range-more-than-one-column.html)

John Mitchell

Sumif where sum range is more than one column?

I am familiar with using SUMIF to conditionally sum a column of data:

=SUMIF(A1:A10,"YES",B1:B10)

To conditionally sum data in columns B, C, and D, I can:

=SUMIF(A1:A10,"YES",B1:B10)+SUMIF(A1:A10,"YES",C1: C10)+SUMIF(A1:A10,"YES",D1:D10)

Is there is a more concise way of doing this with an array formula?

Regards, John M

Peo Sjoblom

One way

=SUMPRODUCT((A1:A10="Yes")*(B1:D10))

--
Regards,

Peo Sjoblom

(No private emails please)

"John Mitchell" wrote in message
...
I am familiar with using SUMIF to conditionally sum a column of data:

=SUMIF(A1:A10,"YES",B1:B10)

To conditionally sum data in columns B, C, and D, I can:

=SUMIF(A1:A10,"YES",B1:B10)+SUMIF(A1:A10,"YES",C1: C10)+SUMIF(A1:A10,"YES",D1:D10)

Is there is a more concise way of doing this with an array formula?

Regards, John M

All times are GMT +1. The time now is 07:05 PM.