SUMPRODUCT - Help

[ceemo]

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

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[ceemo]

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.

 

[Bob Phillips]

I think that was deducible from your previous answers

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[R..VENKATARAMAN]

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?

 

[Peo Sjoblom]

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

 

[ceemo]

all sorted now thank you for everyones help

 

[R..VENKATARAMAN]

thanks understood.

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

 

[Bob Phillips]

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]

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

 

[Aladin Akyurek]

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

 

[bri4eng]

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

 

[SteveG]

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

 

[Bob Phillips]

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)

 

[ceemo]

what do the double minus signs (--) represent

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[SteveG]

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