Forum Discussion
Create Formula that will review three pieces of criteria and return a value
=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.
=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.
- Thank you! I will try this! I sincerely appreciate it!
