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

Question

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

question from:https://stackoverflow.com/questions/65887622/how-to-count-multiple-ranges-that-have-a-status-next-to-them

Answer 1

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")