excelexcel-formulasumproduct

Why is SUMPRODUCT not calculating correctly?

I'm trying to write a formula where it sums if the name is on both sheets and has a string of "Intake Completed" and "Yes"

Here is the sample date for each sheet.

Sheet1
Sheet1

Name
D Intake Completed
A Intake Completed
C, D Intake Completed
P Not
Z Intake Completed

Here is the sample data for my Test sheet.
Test Sheet

Testing Intake Completed
Test, Tester Intake Completed
Brian,
Luke
Perry Intake Completed
T, Z Intake Completed

Here is the Sheet2 that should be comparing as well.

Sheet2

Name Help?
D Yes
A Yes
C, D Yes
P Yes
Testing Yes
Perry Yes
Z Yes
T, Z

Here is the formula that results in 7.

=SUMPRODUCT(--(Sheet1!B:B="Intake Completed"), --(ISNUMBER(MATCH(Sheet1!A:A, Sheet2!A:A, 0))), --(Sheet2!B:B="Yes")) + SUMPRODUCT(--(Test!B:B="Intake Completed"), --(ISNUMBER(MATCH(Test!A:A, Sheet2!A:A, 0))), --(Sheet2!B:B="Yes"))

The result should be 6 as Name P on Sheet1 does not have the value of "Intake Completed".

Solution

  • Edit: The issue wasn't a version or syntax, but logic. So each sumproduct had 3 seperate logic, but really you wanted 2, one being multi-conditional. First check that it says intake completed. Then you want to check if the lookups for those say "Yes", then you want to add that to the same logic in the second set of data, which should have the same logic as well.

    Below you can see I combine the criteria 1 & 2, but the 2nd criteria I lookup and validate the result. Before you we just checking if it said intake completed, existed in the other data, and if the row said "yes" without seeing if the relevant row said "Yes", which is where the disconnect occurred.

    =SUMPRODUCT( (Sheet1!B:B="Intake Completed") * (xlookup(Sheet1!A:A, Sheet2!A:A,Sheet2!B:B,0 0)="Yes")) + SUMPRODUCT( (Test!B:B="Intake Completed") * (xlookup(Test!A:A, Sheet2!A:A,Sheet2!B:B,0, 0)))

    So we see intake completed, then check if the same record also says Yes in the other dataset.