Forum Discussion
Solved
Create Formula that will review three pieces of criteria and return a value
Good Evening Excel Experts, I am requesting your assistance after spending several hours trying to create a specific formula. I’ve looked into V-Lookup; Index & Match and even X-Lookup (new to ...
=SUMPRODUCT(($A6='Master sheet'!$B$2:$B$185107)*(B$4='Master sheet'!$A$2:$A$185107)*('Master sheet'!$F$2:$F$185107=1)*('Master sheet'!$E$2:$E$185107))
I suggest this formula in B6 and copy across range B6:F64.
In order to perform the partial match i added column F in Master sheet with the following formula in F2 (copy down to F185107):
=COUNT(FIND('Store list'!$A$1,$C2))
This formula needs to be entered as matrix with ctrl+shift+enter.
As there a duplicate entries in the Master sheet, the results returned by SUMPRODCUT formula are 0, 100 , 200, 300 .... up to 1000.
Problem: There are multiple matches.
Okay, I'll try adding helpers to the table to identify only one result.