I have an excel file with multiple sheets named 1,2,3...31 (representing days in a month). The table you see below is on every page, but with different values.
RDC 1 5000
RDC 2 0
CAPS 0
TILT 0
FOAM 12500
I want to count the positive values for each option. So if CAPS has in 20 sheets positive values it should return "20". I have no problem doing so for the last 3 options. I used the forumla:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A32&"'!B3");">0"))
But the first two options I want to count them as one. So if:
on day 1 RDC1 has positive value and RDC2 has value 0 it should count as 1
on day 2 both RDC1 & RDC2 have positive values, so it should count as 1 as well, not 2
- on day 3 both RDC1 & RDC2 have value 0, so it should count as 0
I have tried using the formula (and formatting the total count cell to Number with no decimals, so when it's 0.5 it should return 1 )
=SUM(IF('1'!B1>0;1;0);IF('1'!B2>0;1;0))/2 + ... for all days ... + SUM(IF('31'!B1>0;1;0);IF('31'!B2>0;1;0))/2
It's working with fewer sheets, but when I write the formula for all the sheets it returns an error. And yeah... it's a very long formula.
Can anyone help me with a solution to count the positive values together for the first two options as described above?
'1:31'!B3
doesn't work (I'm editing the first post with the picture). I just want to count RDC 1&2 together as described above. Forget about the other options, I just described the whole document to make it clear.