SumIf formula from one column based on criteria from two other
This actually worked for me once.
However, it didn't the second time I tried to use it in another cell to come
up with the statistical totals I was looking for.
Is there any reason why it would work once and not again? The only
difference is that the first time there were multiple criteria in the second
array and in the second time there was only one reference in the second
array. The first and third array's remained identical and didn't change. I
checked and double checked the total on the first time and it was accurate.
Can you see what I did and tell me what I did wrong the second time?
i.e., the two formulas are below:
First time - it worked fine:
=SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ"))
Second time:
=SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of
GA / ABC of MA")+($J1004:$J1053="XYZ"))
"David Biddulph" wrote:
=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink"))
--
David Biddulph
"stanasia" wrote in message
...
Unfortunately this did not assist me in getting a subset of totals based
on
the other criteria in the second and third column. What I need to do is
compare criteria from column B against criteria in Column C and then sum
those adjacent Column A amounts. But I am having trouble writing this
formula.
"Bob Phillips" wrote:
Bit of overkill
=SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green"))
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green"))
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"stanasia" wrote in message
...
Hello,
I am knowledgable how to get a SumIf from one column based one or more
criterias from only one other column. However, now I need to compare
criteria
from two different columns and return the SumIf from a third column. I
tried
Concantenation, but to no avail and even tried nesting If functions
but
still
no avail. Can anyone help?
Below is the scenario:
Col A Col B Col C
52 Winter Pink
23 Spring Pink
6 Fall Green
10 Winter Blue
30 Summer Green
10 Fall Blue
5 Spring Green
45 Winter Green
80 Fall Pink
12 Summer Pink
34 Summer Blue
Now how do I return the sum of Col A for all criteria that meets
Spring =
Green or Pink + Fall = Green or Pink based on Col's B & C?. And only
the
sum
of those criterias?
|