COUNTIFS with 3 ranges

kcaudill1989

I am creating Dashboard Metrics based on Status information in a sheet. The sheet has a Status Column, Parent Status Column and a helper Column "Parent" that is Zero if it has children records or if it has no children records.

If I use the below formula, my result is 2, which is correct for the "Status" column:

=COUNTIFS({Project List Range 5 Parent}, =0, {Project List Range 3 Status}, "Not Started")

If I use the below formula, my result is 10, which is correct for the "Parent Status" column:

=COUNTIFS({Project List Range 5 Parent}, =0, {Project List Range 6 Parent Status}, "Not Started")

However, if I combine them, my result is 0:

=COUNTIFS({Project List Range 5 Parent}, =0, {Project List Range 6 Parent Status}, "Not Started", {Project List Range 3 Status}, "Not Started")

I am looking for a result of 12, what am I missing?

Nick Korna

Hi @kcaudill1989,

At best guess you don't have any rows which fit the merged criteria (but difficult to know without seeing some data).

If the 2 separate formulas are working and would give the correct total when added together you could use this rather than needing to combine into a single one.

=COUNTIFS({Project List Range 5 Parent}, =0, {Project List Range 3 Status}, "Not Started") + COUNTIFS({Project List Range 5 Parent}, =0, {Project List Range 6 Parent Status}, "Not Started")

Nick Korna

Hi @kcaudill1989,

At best guess you don't have any rows which fit the merged criteria (but difficult to know without seeing some data).

If the 2 separate formulas are working and would give the correct total when added together you could use this rather than needing to combine into a single one.

=COUNTIFS({Project List Range 5 Parent}, =0, {Project List Range 3 Status}, "Not Started") + COUNTIFS({Project List Range 5 Parent}, =0, {Project List Range 6 Parent Status}, "Not Started")

kcaudill1989

That worked perfectly.

Thank you!