Formula - Combining sumproduct and indexmatch

[NathanA]

I am using a sumproduct array formula to calculate the cost of a course and primarily have the raw data on sheet 'Data'. However, on sheet 'List', there is a growing list with the unique code of each student and whether the cost has already been paid.


Replica of sheet 'List':


Contact ID Payment delivered?


CON-X
CON-Y
CON-Z
CON-A Yes
CON-B Yes
CON-C Yes


If there isn't a "Yes", I'd like the cost calculated from sheet 'Data' as per the formula below.

={0.8*(0.4*(((SUMPRODUCT((('Data'!$B:$B="Thoroughbred Racing")+('Data'!$B:$B="Equine Breeding"))*('Data'!$I:$I="No")*('Data'!$M:$M<>"Yes")*(TEXT('Data'!$E:$E,"mmmm")&" "&TEXT('Data'!$E:$E,"yyyy")=B$1),'Data'!$K:$K,'Data'!$L:$L))/12)*3200))}

I have tried using indexmatch as another variable in the formula and combined with an if statement, but it hasn't worked since I'm looking at an entire column as a lookup value.

 

[James006]

Hello,

If I understand correctly ... just create a Dynamic Named Range in your sheet 'List' ...

403 Forbidden

HTH

 

[NathanA]

Thanks for replying. I have named the ranges on 'List', but I don't understand how to match the data back to sheet 'Data' in the above formula. Is there a way to incorporate it directly into the above formula without using index match on sheet 'Data'?