How to count multiple ranges that have a status next to them?

user_15077839 0
Viewed 44

I am attempting to count multiple matching ranges that also contain "Done" next to them.

Currently, I am able to use this query to count the Totals, but can't figure out how to make sure it only counts the ones that are "Done"

=SUMPRODUCT(COUNTIF('Subject'!A:A,'Jobs'!A:A))

This is the desired result:

Results Sheet result sheet screenshot

google-sheets-formula google-sheets
1 Answers

You should use VLOOKUP with ARRAYFORMULA to fetch the statuses of the column and COUNTIF to match only the "Done" status.

For First:

=COUNTIF(ARRAYFORMULA(VLOOKUP(Subject!A:A, Jobs!A:B, 2, FALSE)), "Done")

For Second:

=COUNTIF(ARRAYFORMULA(VLOOKUP(Subject!B:B, Jobs!A:B, 2, FALSE)), "Done")

output

user_161186860
Related