Sumdata based on a table of possible criteria

[davew]

Hi- please could you help…

I’d like to create a summary table (using custom formulas) that works along the principles of the Excel function sumif but rather than using references typed directly into a formula or typed into cells e.g. =SUMIF(A6:A15,"Bee",B6:B15) I’d like the formula to reference from a table (named range). I’d like this to allow me to sum data based on a value fitting one of multiple criteria.

Below is an example of what I mean. Here I’ve only one table ‘InsectTable’ and I want to sum only the rows categorised as insects.

Insect Table
Bee
Fly
Moth
Butterfly

Summary:
.....................Day1..Day2..Day3
Insect (Sum)......3.......1.......2....

RawData:
.....................Day1..Day2..Day3
Bee..................1.......1............
Cod........................................
Spanial................................1..
Fly...................1.................1..
Eel...................1.......1...........
Poodle....................................
Moth................1.................1...
Labrador....................1............
Butterfly.................................
Tuna................1.....................
Retriever...........1....................

The attached example illustrates more clearly what I mean. The idea is that I type into a cell a formula that looks like a sumif perhaps but along the lines of =SUMIF(A6:A15,InsectRange,B6:B15)

I’d appreciate any assistance but if you could tailor your answer around my example file that would be great.

I want to avoid using Excel array functions (Crtl+Shift+Enter) as I’ve tried them in the past and I’m not a fan.

Really hope you can help.

davew

[p45cal]

B2 copied across:
=SUMPRODUCT(--ISNUMBER(MATCH(Summary!$A$6:$A$17,Tables!$A$2:$A$5,0)),B$6:B$17)
B3 copied across:
=SUMPRODUCT(--ISNUMBER(MATCH(Summary!$A$6:$A$17,Tables!$B$2:$B$5,0)),B$6:B$17)
B4 copied across:
=SUMPRODUCT(--ISNUMBER(MATCH(Summary!$A$6:$A$17,Tables!$C$2:$C$6,0)),B$6:B$17)

[davew]

Thanks very much p45cal. This works great and no VBA used!