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?

Improve this question
2
  • 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
    Commented Apr 8, 2016 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.
    – Service Desk Bunny
    Commented Apr 8, 2016 at 9:46

1 Answer 1

Reset to default
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.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .