davew
10-05-2012, 02:25 PM
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
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