countifs <>0 not working

[akswartz85]

Hi, I've got a countsif <>0 formula that isn't working.

I'm counting across a big number of columns, and it's every 3rd column. I've got:

=COUNTIFS(BU3,"<>0",BX3,"<>0",CA3,"<>0",CD3,"<>0",CG3,"<>0",CJ3,"<>0",CM3,"<>0",CP3,"<>0",CS3,"<>0",CV3,"<>0",CY3,"<>0",DB3,"<>0",DE3,"<>0",DH3,"<>0",DK3,"<>0",DN3,"<>0",DQ3,"<>0",DT3,"<>0",DW3,"<>0",DZ3,"<>0",EC3,"<>0",EF3,"<>0",EI3,"<>0",EL3,"<>0",EO3,"<>0",ER3,"<>0",EU3,"<>0",EX3,"<>0",FA3,"<>0",FD3,"<>0")

However, the count returns 0. (That is incorrect... it should be at least 1 or more). I checked my cell format, and everything is saved as a number. I tried saving + reopening, removing autofilters (I saw that on another MrExcel post). It's a big file so I let it process while I had lunch (natta).

Any ideas? Thanks.

 

[Joe4]

I am not sure that you are using COUNTIFS correct.
The way you have written it, since you are only checking a single cell in each criteria (and not a whole range), it can only return 1 or 0.
And it will only return one if EVERY one of those Criteria is true (meaning EVERY single cell in your formula is not equal to zero).
If at least one of those cells is zero, it will return 0.

 

[Dryver14]

Thanks for that explanation Joe, I know it's not my question but I have searched everywhere for an answer to this I know I would probably do a simple macro to solve it but am keen to learn excel formulas too.

I would be keen to see if such a search could be done.

 

[Joe4]

Thanks for that explanation Joe, I know it's not my question but I have searched everywhere for an answer to this I know I would probably do a simple macro to solve it but am keen to learn excel formulas too.

I would be keen to see if such a search could be done.

I am first trying to understand exactly what it is that they are trying to do.
It seems to me that they may possibly trying to count the non-zero entries in the individual cells that they have listed, but I cannot say for sure.

 

[Dryver14]

OK I'll wait for the posters response I agree with you but results could be positive or negative else greater than zero would be a starting point

 

[Joe4]

but results could be positive or negative else greater than zero would be a starting point

OK, now I am not sure what you are talking about.
If you are counting the number of values meeting a certain criteria, how could you return a negative value?

Maybe we should have that discussion elsewhere, so this thread does not get "hijacked", as it has not been solved yet.

 

[Joe4]

Back to the original question.

If your intention is to count how many cells in your list BU3, BX3, CA3, ..., FD3) are not zero (that is, every third column starting in BU3 and ending in FD3 that are not empty), you can use this formula:

=SUMPRODUCT(--(BU3:FD3<>0),--(MOD(COLUMN(BU3:FD3),3)=1))

 

[akswartz85]

This is what I'm trying to do - count how many cells in the range are <> 0. I misunderstood the countifs! Thank you.

Back to the original question.

If your intention is to count how many cells in your list BU3, BX3, CA3, ..., FD3) are not zero (that is, every third column starting in BU3 and ending in FD3 that are not empty), you can use this formula:

=SUMPRODUCT(--(BU3:FD3<>0),--(MOD(COLUMN(BU3:FD3),3)=1))

 

[akswartz85]

Can you explain the formula? I want to make sure I understand it correctly

Back to the original question.

If your intention is to count how many cells in your list BU3, BX3, CA3, ..., FD3) are not zero (that is, every third column starting in BU3 and ending in FD3 that are not empty), you can use this formula:

=SUMPRODUCT(--(BU3:FD3<>0),--(MOD(COLUMN(BU3:FD3),3)=1))

 

[Aladin Akyurek]

Are the values of interest (values that must be counted) text or number?

If numbers...

=SUMPRODUCT(--ISNUMBER(1/BU3:FD3),--(MOD(COLUMN(BU3:FD3)-COLUMN(BU3),3)=0))

If text...

=SUMPRODUCT(--(LEN(BU3:FD3)>0),--ISTEXT(BU3:FD3),--(MOD(COLUMN(BU3:FD3)-COLUMN(BU3),3)=0))

If either and you don't want to count empty cells...

=SUMPRODUCT(1-(BU3:FD3=""),--(MOD(COLUMN(BU3:FD3)-COLUMN(BU3),3)=0))

The set up is robust against column insertions in front of current BU.