SumIf formula from one column based on criteria from two other col

[stanasia]

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?

 

[Bob Phillips]

=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]

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)

 

[stanasia]

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.

 

[David Biddulph]

=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink"))

 

[stanasia]

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")+(G1004: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]

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.]

 

[stanasia]

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.

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

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")+(G1004: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"))

 

[Bob Phillips]

=SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC
of GA / ABC of MA","XYZ"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

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.

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

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")+(G1004: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"))

:

=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink"))
--
David Biddulph

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.

:

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)



=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)



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?

 

[stanasia]

Hmmmmm. . . .

I tried it but this time it gave me a value error message.

the other way worked, but was just inaccurate. Any other ideas on how to put
forth the math?

thanks for all your help guys.

=SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC
of GA / ABC of MA","XYZ"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

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.

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

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")+(G1004: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"))

:

=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink"))
--
David Biddulph

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.

:

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)



=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)



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?

 

[stanasia]

Wow!

I finally got the formula to work in all aspects of the data I was trying to
extract!

And it's now accurate - only I had to trick it.

Here's what I did:

Since it had to include a boolean operation when there was only one category
to find, I created a false one and told it to add it each time (of course it
would add '0' because there was not category in the range called "1,2,3":

=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))


Now I need help in counting each time that data occures.

I realize there is no =CountProduct operation. I tried this but it didn't
work - I think I am close - can anyone help me?

=Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC
of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")))

Please????

Hmmmmm. . . .

I tried it but this time it gave me a value error message.

the other way worked, but was just inaccurate. Any other ideas on how to put
forth the math?

thanks for all your help guys.

=SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC
of GA / ABC of MA","XYZ"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

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.

:

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

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")+(G1004: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"))

:

=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink"))
--
David Biddulph

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.

:

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)



=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)



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?

 

[David Biddulph]

It was pointless to include your +($J$1004:$J$1053="1,2,3") as a means of
adding zero. You could have just used +0 (or *1) but the usual
recommendation is to use the double unary minus so you could have had ...
,--($J$1004:$J$1053="ABC of GA / ABC of MA")

To answer your second question, if you don't want to add up the values in
column A, leave that term out.
=SUMPRODUCT(($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),--($J$1004:$J$1053="ABC
of GA / ABC of MA"))
or
=SUMPRODUCT((($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"))*($J$1004:$J$1053="ABC
of GA / ABC of MA"))
--
David Biddulph

Wow!

I finally got the formula to work in all aspects of the data I was trying
to
extract!

And it's now accurate - only I had to trick it.

Here's what I did:

Since it had to include a boolean operation when there was only one
category
to find, I created a false one and told it to add it each time (of course
it
would add '0' because there was not category in the range called "1,2,3":

=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC
of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))


Now I need help in counting each time that data occures.

I realize there is no =CountProduct operation. I tried this but it didn't
work - I think I am close - can anyone help me?

=Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC
of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")))

Please????

Hmmmmm. . . .

I tried it but this time it gave me a value error message.

the other way worked, but was just inaccurate. Any other ideas on how to
put
forth the math?

thanks for all your help guys.

=SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC
of GA / ABC of MA","XYZ"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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.

:

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

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")+(G1004: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"))

:

=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink"))
--
David Biddulph

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.

:

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)



=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)



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?

 

[stanasia]

Oh, cool.

I didn't know I could have used '0' - but that makes a lot of sense. I tried
the double unary minus, but the totals were wrong when I did that, and
sometimes it returned '0' for the total.

I'm going to go back and change the "1,2,3" for '0'. That will make it less
confusing for anyone else that may need to change it in the future.

As for my counting formula, what to do about that one?

How can I do basically the same thing but count the number of times each
item occurs instead of the total amount of numerical value for each
occurance.

i.e.,

4 apples, and 6 bananas, and 3 more apples that are matching the other
criteria of ="ABC of GA / ABC of MA". I don't need the sum this time. Just
the fact that apples happened 2 times and bananas happened 1 time.

Is there a twist on this that includes Countif?

It was pointless to include your +($J$1004:$J$1053="1,2,3") as a means of
adding zero. You could have just used +0 (or *1) but the usual
recommendation is to use the double unary minus so you could have had ...
,--($J$1004:$J$1053="ABC of GA / ABC of MA")

To answer your second question, if you don't want to add up the values in
column A, leave that term out.
=SUMPRODUCT(($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),--($J$1004:$J$1053="ABC
of GA / ABC of MA"))
or
=SUMPRODUCT((($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"))*($J$1004:$J$1053="ABC
of GA / ABC of MA"))
--
David Biddulph

Wow!

I finally got the formula to work in all aspects of the data I was trying
to
extract!

And it's now accurate - only I had to trick it.

Here's what I did:

Since it had to include a boolean operation when there was only one
category
to find, I created a false one and told it to add it each time (of course
it
would add '0' because there was not category in the range called "1,2,3":

=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC
of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))


Now I need help in counting each time that data occures.

I realize there is no =CountProduct operation. I tried this but it didn't
work - I think I am close - can anyone help me?

=Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC
of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")))

Please????

Hmmmmm. . . .

I tried it but this time it gave me a value error message.

the other way worked, but was just inaccurate. Any other ideas on how to
put
forth the math?

thanks for all your help guys.

:

=SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC
of GA / ABC of MA","XYZ"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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.

:

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

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")+(G1004: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"))

:

=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink"))
--
David Biddulph

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.

:

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)



=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)



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?

 

[David Biddulph]

You are obviously having difficulty in reading the replies you have been
given.
--
David Biddulph

Oh, cool.

I didn't know I could have used '0' - but that makes a lot of sense. I
tried
the double unary minus, but the totals were wrong when I did that, and
sometimes it returned '0' for the total.

I'm going to go back and change the "1,2,3" for '0'. That will make it
less
confusing for anyone else that may need to change it in the future.

As for my counting formula, what to do about that one?

How can I do basically the same thing but count the number of times each
item occurs instead of the total amount of numerical value for each
occurance.

i.e.,

4 apples, and 6 bananas, and 3 more apples that are matching the other
criteria of ="ABC of GA / ABC of MA". I don't need the sum this time. Just
the fact that apples happened 2 times and bananas happened 1 time.

Is there a twist on this that includes Countif?

It was pointless to include your +($J$1004:$J$1053="1,2,3") as a means of
adding zero. You could have just used +0 (or *1) but the usual
recommendation is to use the double unary minus so you could have had ...
,--($J$1004:$J$1053="ABC of GA / ABC of MA")

To answer your second question, if you don't want to add up the values in
column A, leave that term out.
=SUMPRODUCT(($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),--($J$1004:$J$1053="ABC
of GA / ABC of MA"))
or
=SUMPRODUCT((($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"))*($J$1004:$J$1053="ABC
of GA / ABC of MA"))
--
David Biddulph

Wow!

I finally got the formula to work in all aspects of the data I was
trying
to
extract!

And it's now accurate - only I had to trick it.

Here's what I did:

Since it had to include a boolean operation when there was only one
category
to find, I created a false one and told it to add it each time (of
course
it
would add '0' because there was not category in the range called
"1,2,3":

=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC
of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))


Now I need help in counting each time that data occures.

I realize there is no =CountProduct operation. I tried this but it
didn't
work - I think I am close - can anyone help me?

=Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC
of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")))

Please????



:

Hmmmmm. . . .

I tried it but this time it gave me a value error message.

the other way worked, but was just inaccurate. Any other ideas on how
to
put
forth the math?

thanks for all your help guys.

:

=SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC
of GA / ABC of MA","XYZ"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



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.

:

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

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")+(G1004: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"))

:

=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink"))
--
David Biddulph

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.

:

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)



=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)



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?