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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =sumproduct(--(columnA={"Green","Blue"}))
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ceemo" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

[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?


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =sumproduct(--(columnA={"Green","Blue"}))
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ceemo" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>

[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




"R..VENKATARAMAN" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> =sumproduct(--(columnA={"Green","Blue"}))
>>
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from email address if mailing direct)
>>
>> "ceemo" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> 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
>>>
>>
>>
>
>

[ceemo]

all sorted now thank you for everyones help

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > =sumproduct(--(columnA={"Green","Blue"}))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "ceemo" <[email protected]> wrote in
> > message news:[email protected]...
> >>
> >> 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
> >>
> >
> >
>
>

[R..VENKATARAMAN]

thanks understood.

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>> news:[email protected]...
>>> =sumproduct(--(columnA={"Green","Blue"}))
>>>
>>>
>>> --
>>> HTH
>>>
>>> Bob Phillips
>>>
>>> (remove nothere from email address if mailing direct)
>>>
>>> "ceemo" <[email protected]> wrote in
>>> message news:[email protected]...
>>>>
>>>> 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
>>>>
>>>
>>>
>>
>>
>

[R..VENKATARAMAN]

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


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > =sumproduct(--(columnA={"Green","Blue"}))
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from email address if mailing direct)
>> >
>> > "ceemo" <[email protected]> wrote in
>> > message news:[email protected]...
>> >>
>> >> 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
>> >>
>> >
>> >
>>
>>
>
>

[Aladin Akyurek]

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

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

"SteveG" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[ceemo]

what do the double minus signs (--) represent

[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

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

"ceemo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>