# Count values from multiple sheets in Excel

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?

• your question is interesting, and you've already achieved a lot. However due to its complexity it's difficult to understand, could you please post a few screenshots? (e.g. what's in A2:A32 from first formula)? Do you want to count only RDC 1 & 2 together, and all the others separately, or everything together? How your summary sheet would look like? – Máté Juhász Apr 8 '16 at 9:28
• Thank you for replying so fast @MátéJuhász! In A2:A32 I've placed the "names" of the sheets. I've done it this way because as I recall `'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. – User552853 Apr 8 '16 at 9:46

Instead of `COUNTIF` you can use `COUNTIFS`:
`=COUNTA(A2:A32)-SUMPRODUCT(COUNTIFS(INDIRECT("'"&A2:A32&"'!B1");"=0";INDIRECT("'"&A2:A32&"'!B2");"=0"))`
Note that `COUNTIF` performs an AND operation, so if you want to count when B1 OR B2 is >0 then just count when both are equal to 0 and subtract this number from the total count.