Wow! This is really cool - only the sumtotal was inaccurate - instead of
giving me the correct number of 24 - it gave me the wrong total of 59.

I used the * function instead of the , between array 2 & 3. When I used the
- - - it gave me a number in the 220's.

How can I use this formula to get the right number?

Thanks for the lesson - you really know your stuff!

now if I can just get it right.

"David Biddulph" wrote:

In your "second time" formula you haven't coerced the
($G1004:$G1053="Bananas") term from Boolean to a number. You either need a
multiply instead of the comma separating the arguments in the SUMPRODUCT
expression, or otherwise the double unary minus construct.

[In the "first time" formula you've done arithmetic which forces the Boolean
to a numeric value.]
--
David Biddulph

"stanasia" wrote in message
...
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?