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]

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

People often ask about the double-negatives. It is because each piece returns TRUE/FALSE and the double-negatives coerces them to their numeric counterparts of 1 and 0.
There is a lot more information regarding SUMPRODUCT here:
https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

Regarding my formula:

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

The first part simply checks for a numeric non-zero value.
The second part if checking column number. The MOD function returns the remainder when divided by 3 (see: https://www.techonthenet.com/excel/formulas/mod.php)
Your first column is BU, which is column number 73, next is BX, which is 76, then CA, which is 79, etc
As you mentioned, they increase by 3 each time. If you divide those numbers by 3, you get a remainder of 1. So those are the column numbers that we want to include (and exclude the rest).

Does that make sense?

 

[akswartz85]

People often ask about the double-negatives. It is because each piece returns TRUE/FALSE and the double-negatives coerces them to their numeric counterparts of 1 and 0.
There is a lot more information regarding SUMPRODUCT here:
https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

Regarding my formula:

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

The first part simply checks for a numeric non-zero value.
The second part if checking column number. The MOD function returns the remainder when divided by 3 (see: https://www.techonthenet.com/excel/formulas/mod.php)
Your first column is BU, which is column number 73, next is BX, which is 76, then CA, which is 79, etc
As you mentioned, they increase by 3 each time. If you divide those numbers by 3, you get a remainder of 1. So those are the column numbers that we want to include (and exclude the rest).

Does that make sense?

Yes, that does. I followed the first part, but not the second. Now I do. Thank you!

 

[Joe4]

You are welcome!

 

[akswartz85]

You are welcome!

Actually - quick clarification on this: The MOD function returns the remainder when divided by 3 (see: https://www.techonthenet.com/excel/formulas/mod.php)

If I want to shift it to every 4th column, what is the numerator I use? Do I count the number of cols between start and finish? Or do I just shift it to 4?

 

[Joe4]

Whatever your column "jump" is, that is the number you want to use as the second argument in the MOD function (the divisor). So you would change that to 4.
But you still need to figure out what remainder you are checking for. So, identify the first column BU, and get the column number.
(If you are not sure off the top of your head what that is, go to any row in that column, and enter this formula: =COLUMN()).
Then, do the math. What is the remainder when you divide 73 by 4? It is still 1, so you would use:

MOD(COLUMN(BU3:FD3),4)=1

 

[akswartz85]

Whatever your column "jump" is, that is the number you want to use as the second argument in the MOD function (the divisor). So you would change that to 4.
But you still need to figure out what remainder you are checking for. So, identify the first column BU, and get the column number.
(If you are not sure off the top of your head what that is, go to any row in that column, and enter this formula: =COLUMN()).
Then, do the math. What is the remainder when you divide 73 by 4? It is still 1, so you would use:

MOD(COLUMN(BU3:FD3),4)=1

ok, this is how i thought it worked. THANKS!

 

[Joe4]

ok, this is how i thought it worked. THANKS!

Because the "jump" is the same as your divisor, the remainder will never change, i.e.
MOD(73,4)=1
MOD(77,4)=1
MOD(81,4)=1
etc
That is why/how that works.

 

[akswartz85]

Because the "jump" is the same as your divisor, the remainder will never change, i.e.
MOD(73,4)=1
MOD(77,4)=1
MOD(81,4)=1
etc
That is why/how that works.

So, not quite sure what is going on. When I drag it over to the right - I want to count the # of cells at 0-12 months, 13-24, 25-36, so 3 different time points - it isn't counting. I didn't add any $ or modify the formula in any way. Thoughts?

 

[Joe4]

So, not quite sure what is going on. When I drag it over to the right - I want to count the # of cells at 0-12 months, 13-24, 25-36, so 3 different time points - it isn't counting. I didn't add any $ or modify the formula in any way. Thoughts?

I am not sure that I follow.
What are the ranges for these different monthly bands?