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

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
Maybe
=IF($Q47="Yes",SUMPRODUCT(('Tabbing Data'!$A$1:$CV$1=$P47)*('Tabbing Data'!$A$2:$CV$5000)),"")
 

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
Glad you sorted it & thanks for the feedback
 

Forum statistics

Threads
1,084,838
Messages
5,380,198
Members
401,654
Latest member
etusch

Some videos you may like

This Week's Hot Topics

Top