COUNTIF showing "0" when referencing a column where cells have formula

JoeN

Hello!

Please help! First I have one sheet (Pre-Service) with several columns, three of them used here, the first two are dates, and the third is a calculation to determine the number of weeks between the two dates. I used (=(([Anticipated Graduation date]@row) - ([Pre-Service Class Start Date]@row)) / 7).

So far so good. When using a calculation sheet, to group the number of weeks for the entire column (to create a chart), with the calculation: =COUNTIF({Pre-Service Range 4}, "7") the result is "0". I have also tried =COUNTIF({Pre-Service Range 4}, "=7"), =COUNTIF({Pre-Service Range 4}, 7) and they all return "0".

JoeN

Finally figured it out. Count if won't work if there are decimals places unless using round. The formula above will look like: = Round(([Anticipated Graduation date]@row) - ([Pre-Service Class Start Date]@row)) / 7)

I really appreciate your time looking at this!!

JoeN

Also, if I type the number on column Week in PS under the Pre-Service sheet, rather than a formula, it will take just fine when doing COUNTIF.

Jeff Reisman

Try using

=COUNTIF({Pre-Service Range 4}, =7)

or =COUNTIF({Pre-Service Range 4}, @cell = 7)

JoeN

Thanks for replying Jeff! I tried it, but didn't work. What puzzle's me is that if I type the number on the reference column (Pre-Service) rather than having a formula, it'll take it just fine.

JoeN

Also, if the result or total on the reference table (Pre-Service) is "0" it'll bring with formula, but if other number greater than "0" it won't.

Paul Newcome

What happens if you wrap the Week in PS formula in a VALUE function

=VALUE(([Anticipated Graduation date]@row - [Pre-Service Class Start Date]@row) / 7)

And then leave the quotes off in the COUNTIFS

=COUNTIFS({Pre-Service Range 4}, @cell = 7)

JoeN

Finally figured it out. Count if won't work if there are decimals places unless using round. The formula above will look like: = Round(([Anticipated Graduation date]@row) - ([Pre-Service Class Start Date]@row)) / 7)

I really appreciate your time looking at this!!

Paul Newcome

I didn't think about that yet, but it does make sense that was the issue. It will work with decimals so long as you are searching for that. If you are searching for 7 and the cell contains 7.1, it won't match up and you'll get a zero count unless you actually search for 7.1 even if you have it set to not display decimals.