Counting how many occurences with multiple columns.

[LBROWN9]

I need to create a formula to count 1, if Column A has "WH" and a number greater than zero in Column B or Column C.


I currently have
=COUNTIFS(Table1[ColumnA],"WH",Table1[ColumnB],">0") and it populates correctly, but some lines will have data in both and I don't want them to add.

=COUNTIFS(Table1[ColumnA],"WH",Table1[ColumnB]:Table1[ColumnC],">0") gives me an error.

 

[bosco_yip]

Hi,

Upload a sample file, give the input table and the desired output results.

Regards

 

[vletm]

change
=COUNTIFS(Table1[ColumnA],"WH",Table1[ColumnB]:Table1[ColumnC],">0")
to
=COUNTIFS(Table1[ColumnA],"WH",Table1[ColumnB],">0",Table1[ColumnC],">0")

 

[LBROWN9]

I'm trying to count how many of a specific code has a number greater than zero in either one of the columns. For Category "WH", it's saying 1, when it should say 2, but it's only pulling data from the [RT] column. When I tried to get it to pull for both [RT] and [OT], I get zeros.

 

[bosco_yip]

Maybe …….

=SUMPRODUCT((Table2[Cat.]="WH")*(((Table2[RT]>0)+(Table2[OT])>0)>0))

Regards

 

[LBROWN9]

Maybe …….

=SUMPRODUCT((Table2[Cat.]="WH")*(((Table2[RT]>0)+(Table2[OT])>0)>0))

Regards

It works!! Thank you!!