COUNTIF Formula help

[MROMAR]

I have a table with an array of data, rows S, T, U, V and W all contain numbers.

I need a formula that will count how many times in my table, that the value of these 5 columns in any given row is Zero.
If S2, T2, U2, V2, and W2 all sum to Zero, I need the formula to count that

There are over 10,000 rows and the number varies everyday, it could be anywhere between 10,000 and 14,000.

[Fotis1991]

Do you mean?

=COUNTIF(S2:W1000,0)

[MROMAR]

Do you mean?

=COUNTIF(S2:W1000,0)

That doesn't work because that just counts the total number of zeros in the columns.

I need it only to recognize when S2=0 T2=0 U2=0 V2=0 and W2=0

If any of those value are greater than zero then do not count it, so if S2=5 T2=0 U2=0 V2=0 and W2=0, then that row should not be counted

[zbor]

Try

=SUMPRODUCT(--(S2:S1000* T2:T1000* U2:U1000* V2:V1000* W2:W1000>0)

But this will also blank calculate as 0. Si that OK?

[FCarv]

Helllo,

I think you could use for example the following formula:

Please Login or Register  to view this content.

[zbor]

Hi FCarv, no that won't help.
Each of criteria (S2=0, T2=0 etc) will return you TRUE or FALSE.
So you will basically count TRUE and FALSE in range and then sum it...

[MROMAR]

Try

=SUMPRODUCT(--(S2:S1000* T2:T1000* U2:U1000* V2:V1000* W2:W1000>0)

But this will also blank calculate as 0. Si that OK?

This didn't work either, not having any luck

[FCarv]

Thank you for the tip zbor.
You are totally correct.

Meanwhile I was seeing the formula you used, and seems that the result of your formula will only sum when S2, T2 etc >0, and not =0.
I was trying your formula and seems that it only counts when a row has all the columns with values greater than zero, when it should be the opposite, right?

I did change the formula to:

Please Login or Register  to view this content.

The problem is that it counts the blank cells as you said. So in my case it counts me 999 times because I had no data at that range.

[duanzhuanming]

hi,
you can use formula below:
=SUMPRODUCT((CONCATENATE(S2:S10000,T2:T10000,U2:U10000,V2:V10000,W2:W10000)="00000")*(1))
^0^

[duanzhuanming]

hi,
you can use formula below:
=SUMPRODUCT((CONCATENATE(S2:S10000,T2:T10000,U2:U10000,V2:V10000,W2:W10000)="00000")*(1))
^0^

[MROMAR]

You are correct FCarv, the formula needs to count a row if the values of the colums in that row all equal zero

[Fotis1991]

=if(and(s2=0,t2=0,u2=0,v2=0,w2=0),countif(s2:w1000,0),"")

[FCarv]

Ok, to sum up:

Fotis, your formula will result in counting every zero in that area.
Supposenly you can only count "1" when all the columns are zero, i.e., when Sx=0, Tx=0, Ux=0 and Vx=0, all together.

The formula from duanz does exactly what MROMAR wants but it gives an error (#NUM!) when there is nothing to count. Example: When all the cells from S2:W1000 are different from zero.

The formula zbor suggested with the modification I have made has that flaw that counts the blank cells. But still if you don't have blank cells you will be ok.

[FCarv]

Ok, if you use this one you WILL get the result you want. A small modification to duanz formula:

Please Login or Register  to view this content.

[MROMAR]

Ok, if you use this one you WILL get the result you want. A small modification to duanz formula:

Please Login or Register  to view this content.

It says there is an error

[Fotis1991]

@ FCarv

Fotis, your formula will result in counting every zero in that area.
Supposenly you can only count "1" when all the columns are zero, i.e., when Sx=0, Tx=0, Ux=0 and Vx=0, all together.

Are you sure?

In post#3,MROMAR said:

T

hat doesn't work because that just counts the total number of zeros in the columns.

I need it only to recognize when S2=0 T2=0 U2=0 V2=0 and W2=0

If any of those value are greater than zero then do not count it, so if S2=5 T2=0 U2=0 V2=0 and W2=0, then that row should not be counted

My(second) formula does this.

[duanzhuanming]

It says there is an error

attached file to explain. no error.

[MROMAR]

Works perfect, thank you so much

Everyone's help is highly appreciated.

[FCarv]

It says there is an error

Sorry MROMAR, I forgot you were from Canada, you had to change the ";" between parameters to ",". In Europe we do things slightly different I guess

@ FCarv
Are you sure?

Yes, because it would count every single zero within the range you selected, i.e., S2:W1000.
For the example MROMAR gave it would provide you with the result of 4, because S2=5 and the four remaining columns were zero. And the ressult should be 0 because only when all columns are zero it should provide you with a result of 1

I dunno if I'm being clear but I'm making an effort

[daddylonglegs]

Why not COUNTIFS?, try

=COUNTIFS(S:S,0,T:T,0,U:U,0,V:V,0,W:W,0)

[Marcol]

This will count the rows with 5 zeros and ignore blank rows

Please Login or Register  to view this content.