CODEWITHSUNDEEP
excelexcel-formulacountif
Gary Evans
Gary Evans

Reputation: 1880

CountIf not counting values derived from formula

I couldn't find a matching answer already but happy to be redirected!

My issue is with countifs across two worksheets but I can replicate it in a smaller environment.

I have three columns of data (A-C): -

Picture of data

Column D has the formula =IF(A2="Closed",C2-B2,0).

That bit works, I now need to count how many took X number of days to close: -

Count

Column G has the formula =COUNTIFS(A2:A11,"Closed",D2:D11,F2)

Looking at the pictures 41 and 49 should have a count of 1 right? What have I done wrong? All cells are formated as numbers.

Upvotes: 1

Views: 3205

Answers (3)

Nitesh Halai
Nitesh Halai

Reputation: 927

Check you output in Column D. It must be having decimals. If that is the case, you need to round the formulas in column D using ROUND formula.

=ROUND(IF(A2="Closed",C2-B2,0),0)

Upvotes: 1

teylyn
teylyn

Reputation: 35915

Your formula in column G uses an absolute comparison to the value in column F.

The problem is that none of your values exactly match that value.

The duration column is formatted to show a value rounded to a day, but the underlying value is not the same as what is showing in the formatted cell.

Therefore, the formula in column G needs to factor in a range of values like this:

=COUNTIFS($A$2:$A$11,"Closed",$D$2:$D$11,">="&F2,$D$2:$D$11,"<"&F3)

In words: count all the cells where column A shows "Closed" and where the value in column D is between the value in F2 and the value in F3.

You will need to add an extra value in column F for anything above your biggest number in column F.

Upvotes: 5

ProgSnob
ProgSnob

Reputation: 493

Rows 4 and 7 have status as "Open" and hence won't be counted by Countifs, i. e. change value of cells A4 and A7 to "Closed" to see updated results.

Also, fix your range $A$2:$A$11, etc. when using Countifs

Upvotes: 0

Related Questions