2

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"))

image for above formula

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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.