Sumproduct with multiple criteria - to count numbers greater than zero

Hello everyone,

I am hoping that someone can help me to resolve an issue with counting:

I have a table that has:
columns with weekdays 1-7 for 90 day period
rows with IDs and some of them can be met twice

I need to count a number of cells that meet criteria ID and weekday but only if the number in the range is greater than zero (if ID was active on that day. It can be active, for example, in one row on 1st, 2nd and 6th Mondays of the period and active in another row on the 4th and 7th Monday etc.).

The below formula seems to work but it counts zero and blank amounts:
=SUMPRODUCT(--($CS$6:$CS$5000=CS6)*($D$1:$CO$1=CZ5))

here D1:CO1 is a columns header with weekday 1 to 7, CZ5 is a given weekday (1 to 7)
CS6:CS5000 is a column with IDs and CZ6 is an ID to count the activity days for.

So, is it possible to add another condition to count cells in range D6:CO5000 only if they are greater than zero?

Appreciate any advice

Originally Posted by FlameRetired

Hi CoffeeTea and welcome to the forum,

Would something like this do what you are wanting?

Formula:

Please Login or Register  to view this content.

Yes!!! Brilliant!!!!
Thanks a million FlameRetired!!! You're genius!!!