SUM of Array Where Two Conditions Are Met On Worksheet Other Than Where Array Is Found

[Aimee S.]

I don't know if this is possible, but I have tried and failed to do the following using sumproduct and sumif(s).

Sum all values in array 'Tabbing Data'!$A$2:$CA$5000

Where column header = a value in a reference matrix, e.g. 'Tabbing Data'!$A$1:$CV$1='Data Gap Check'!$P47

And where 'Data Gap Check'!$Q47 = "Yes"

The formula would live in the 'Data Gap Check' sheet.

Any help would be immensely appreciated.
Aimee

 

[Fluff]

Maybe
=IF($Q47="Yes",SUMPRODUCT(('Tabbing Data'!$A$1:$CV$1=$P47)*('Tabbing Data'!$A$2:$CV$5000)),"")

 

[Aimee S.]

Alas, I get a #VALUE ! Thanks and great idea though! I opted to just use an HLOOKUP and hide a total row at line 5000 and locked it so no one can delete it. I hate the way they provide me this data - what a pain! lol

 

[Fluff]

Glad you sorted it & thanks for the feedback