Problem with Sumproduct formula

[dinsyp]

Hi people,

This is baffling me. I am counting the number of rows in a column based on one of three criteria: Completed, In Process, and Not Started. The formula below works well when "Not Started" is replaced by "Completed" or "In Process". But "Not Started" always gives a value of 0.

=SUMPRODUCT(SUBTOTAL(3,OFFSET($G$5:$G$6061,ROW($G$5:$G$6061)-MIN(ROW($G$5:$G$6061)),,1)),--($D$5:$D$6061="Not Started"))

Any ideas?

Regards

Andrew

 

[richardcdahlgren]

Have you checked your "Not Started" selections to make sure there isn't a space before or after Not Started? That would be the simplest thing to check first.

 

[dinsyp]

Yes I thought of that and did a check. Copying the text into the formula also made no difference.

 

[Aladin Akyurek]

Yes I thought of that and did a check. Copying the text into the formula also made no difference.

Do...

=COUNTIFS($D$5:$D$6061,"Not Started")

=COUNTIFS($D$5:$D$6061,"Not*Started")

=COUNTIFS($D$5:$D$6061,"*Not Started*")

all give the same result?

 

[dinsyp]

Those were my original formulae. I did the change so the result would reflect a filter on the data.

I am happy to report though that its now working. Not sure why but I'm not arguing

Thanks for your help.

Andrew

 

[Aladin Akyurek]

Those were my original formulae. I did the change so the result would reflect a filter on the data.

I am happy to report though that its now working. Not sure why but I'm not arguing

Thanks for your help.

Andrew

What was the issue?