Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=513151

=sumproduct(--(columnA={"Green","Blue"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=513151

thanks for your help anyway but youve not quite grassped what i was
after.


column a = apple _and_ column b is either green _or__yellow.


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=513151

I think that was deducible from your previous answers

=sumproduct((columnA=apple)*(columnB={"Green","Blu e"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
=sumproduct(--(columnA={"Green","Blue"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread:
http://www.excelforum.com/showthread...hreadid=513151

I have problem If I use columnA in the formula it gives error--#NAME
If I use actual range for e.g. A1:A10 instead of columnA it works.
mine is XP/excel2002. What mistake am I doing?


"Bob Phillips" wrote in message
...
=sumproduct(--(columnA={"Green","Blue"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread:
http://www.excelforum.com/showthread...hreadid=513151

You need to define a name as ColumnA, I am assuming Bob used this just
because the OP used
it, I am sure the OP is using something else, however to get it to work as
written you need to define a name for the ranges you are using

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"R..VENKATARAMAN" wrote in message
...
I have problem If I use columnA in the formula it gives error--#NAME
If I use actual range for e.g. A1:A10 instead of columnA it works.
mine is XP/excel2002. What mistake am I doing?


"Bob Phillips" wrote in message
...
=sumproduct(--(columnA={"Green","Blue"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread:
http://www.excelforum.com/showthread...hreadid=513151

all sorted now thank you for everyones help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=513151

thanks understood.

"Peo Sjoblom" wrote in message
...
You need to define a name as ColumnA, I am assuming Bob used this just
because the OP used
it, I am sure the OP is using something else, however to get it to work as
written you need to define a name for the ranges you are using

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"R..VENKATARAMAN" wrote in message
...
I have problem If I use columnA in the formula it gives error--#NAME
If I use actual range for e.g. A1:A10 instead of columnA it works.
mine is XP/excel2002. What mistake am I doing?


"Bob Phillips" wrote in message
...
=sumproduct(--(columnA={"Green","Blue"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread:
http://www.excelforum.com/showthread...hreadid=513151

Yeah sorry about not being clear there, as Peo says, I answered in the OP's
style as he had got SUMPRODUCT working, so I assumed that his columnA was a
range within column A.

SUMPRODUCT doesn't work with complete columns, you have to specify a range.
You can get close, like A1:A65535, but not A:A.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"R..VENKATARAMAN" wrote in message
...
I have problem If I use columnA in the formula it gives error--#NAME
If I use actual range for e.g. A1:A10 instead of columnA it works.
mine is XP/excel2002. What mistake am I doing?


"Bob Phillips" wrote in message
...
=sumproduct(--(columnA={"Green","Blue"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo

------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread:
http://www.excelforum.com/showthread...hreadid=513151

thank you Mr. phillips for caring to to clarify to me.


"Bob Phillips" wrote in message
...
Yeah sorry about not being clear there, as Peo says, I answered in the
OP's
style as he had got SUMPRODUCT working, so I assumed that his columnA was
a
range within column A.

SUMPRODUCT doesn't work with complete columns, you have to specify a
range.
You can get close, like A1:A65535, but not A:A.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"R..VENKATARAMAN" wrote in message
...
I have problem If I use columnA in the formula it gives error--#NAME
If I use actual range for e.g. A1:A10 instead of columnA it works.
mine is XP/excel2002. What mistake am I doing?


"Bob Phillips" wrote in message
...
=sumproduct(--(columnA={"Green","Blue"}))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help


--
ceemo

------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread:
http://www.excelforum.com/showthread...hreadid=513151

=SUMPRODUCT(--($A$2:$A$100="apple"),--ISNUMBER(MATCH($B$2:$B$100,{"green","blue"},0)))

ceemo wrote:
Ive started using the sumproduct function to count multiple conditions
which is useful

howveer if i want to count those records in one column that meet a
condition and those records in another column that meet anyone of a
number of conditions how can i do that?


the only way i can think is like the below


=sumproduct(--((columnA=apple)*((ColumnB<Red)*(columnB<Yellow) )))


Rather than having to eliminate red and yellow i would like to say is
green or blue.


Please help

Bob-

Saw your message on a reply to a user looking for a SUMIF type
operation. I've got a similar situation where I have a rather large
data base (~12000 rows) x 48 columns. I've used the SUMIF extensively
to locate and count data from a SINGLE column and it works fine. But
I've got to further refine the searches now to include each STATE that
data comes from. One of the columns in the data array has the state
data listed. What I need to accomplish is to find all the instances of
a lookup value (column A from the report sheet) by using SUMIF from the
data array. Works good. But I need to FILTER the SUMIF command to now
include a type of (IF column R from the data array = "Alabama") then
count that rows figures in the SUMIF command processing.

I'm not having any luck. Any advice??

Brian

Brian,

You should be able to use SUMPRODUCT.

=SUMPRODUCT(--(A1:A10="a"),--(B1:B10="Alabama"))

You can also put your state names in another cell and refer to that
cell rather than typing in the state's name in the formula above.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513151

If it is summing, you need to add a range to sum, like so

=SUMPRODUCT(--($A$1:$A$10="a"),--($B$1:$B$10="Alabama"),$C$1:$C$10)

change the ranges to suit

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SteveG" wrote in
message ...

Brian,

You should be able to use SUMPRODUCT.

=SUMPRODUCT(--(A1:A10="a"),--(B1:B10="Alabama"))

You can also put your state names in another cell and refer to that
cell rather than typing in the state's name in the formula above.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513151

what do the double minus signs (--) represent


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=513151

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ceemo" wrote in
message ...

what do the double minus signs (--) represent


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=513151

Ceemo,

It is called the double unary operator. Check out this site for a more
detailed explanation as well as other uses of SUMPRODUCT.

Cheers,

Steve


http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513151