software

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)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))Rather than having to eliminate red and yellow i would like to say is
green or blue.Please help--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=513151=sumproduct(--(columnA={quot;Greenquot;,quot;Bluequot;}))--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;ceemoquot; gt; wrote in
message ...
gt;
gt; Ive started using the sumproduct function to count multiple conditions
gt; which is useful
gt;
gt; howveer if i want to count those records in one column that meet a
gt; condition and those records in another column that meet anyone of a
gt; number of conditions how can i do that?
gt;
gt;
gt; the only way i can think is like the below
gt;
gt;
gt; =sumproduct(--((columnA=apple)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))
gt;
gt;
gt; Rather than having to eliminate red and yellow i would like to say is
gt; green or blue.
gt;
gt;
gt; Please help
gt;
gt;
gt; --
gt; ceemo
gt; ------------------------------------------------------------------------
gt; ceemo's Profile:
www.excelforum.com/member.php...oamp;userid=10650
gt; View this thread: www.excelforum.com/showthread...hreadid=513151
gt;

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: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=513151I think that was deducible from your previous answers

=sumproduct((columnA=apple)*(columnB={quot;Greenquot;,quot;Blu equot;}))--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Bob Phillipsquot; gt; wrote in message
...
gt; =sumproduct(--(columnA={quot;Greenquot;,quot;Bluequot;}))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;ceemoquot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; Ive started using the sumproduct function to count multiple conditions
gt; gt; which is useful
gt; gt;
gt; gt; howveer if i want to count those records in one column that meet a
gt; gt; condition and those records in another column that meet anyone of a
gt; gt; number of conditions how can i do that?
gt; gt;
gt; gt;
gt; gt; the only way i can think is like the below
gt; gt;
gt; gt;
gt; gt; =sumproduct(--((columnA=apple)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))
gt; gt;
gt; gt;
gt; gt; Rather than having to eliminate red and yellow i would like to say is
gt; gt; green or blue.
gt; gt;
gt; gt;
gt; gt; Please help
gt; gt;
gt; gt;
gt; gt; --
gt; gt; ceemo
gt; gt; ------------------------------------------------------------------------
gt; gt; ceemo's Profile:
gt; www.excelforum.com/member.php...oamp;userid=10650
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=513151
gt; gt;
gt;
gt;
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?quot;Bob Phillipsquot; gt; wrote in message
...
gt; =sumproduct(--(columnA={quot;Greenquot;,quot;Bluequot;}))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;ceemoquot; gt; wrote in
gt; message ...
gt;gt;
gt;gt; Ive started using the sumproduct function to count multiple conditions
gt;gt; which is useful
gt;gt;
gt;gt; howveer if i want to count those records in one column that meet a
gt;gt; condition and those records in another column that meet anyone of a
gt;gt; number of conditions how can i do that?
gt;gt;
gt;gt;
gt;gt; the only way i can think is like the below
gt;gt;
gt;gt;
gt;gt; =sumproduct(--((columnA=apple)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))
gt;gt;
gt;gt;
gt;gt; Rather than having to eliminate red and yellow i would like to say is
gt;gt; green or blue.
gt;gt;
gt;gt;
gt;gt; Please help
gt;gt;
gt;gt;
gt;gt; --
gt;gt; ceemo
gt;gt; ------------------------------------------------------------------------
gt;gt; ceemo's Profile:
gt; www.excelforum.com/member.php...oamp;userid=10650
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=513151
gt;gt;
gt;
gt;
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

quot;R..VENKATARAMANquot; gt; wrote in message
...
gt;I have problem If I use columnA in the formula it gives error--#NAME
gt; If I use actual range for e.g. A1:A10 instead of columnA it works.
gt; mine is XP/excel2002. What mistake am I doing?
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt;gt; =sumproduct(--(columnA={quot;Greenquot;,quot;Bluequot;}))
gt;gt;
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Bob Phillips
gt;gt;
gt;gt; (remove nothere from email address if mailing direct)
gt;gt;
gt;gt; quot;ceemoquot; gt; wrote in
gt;gt; message ...
gt;gt;gt;
gt;gt;gt; Ive started using the sumproduct function to count multiple conditions
gt;gt;gt; which is useful
gt;gt;gt;
gt;gt;gt; howveer if i want to count those records in one column that meet a
gt;gt;gt; condition and those records in another column that meet anyone of a
gt;gt;gt; number of conditions how can i do that?
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; the only way i can think is like the below
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; =sumproduct(--((columnA=apple)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; Rather than having to eliminate red and yellow i would like to say is
gt;gt;gt; green or blue.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; Please help
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; ceemo
gt;gt;gt; ------------------------------------------------------------------------
gt;gt;gt; ceemo's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=10650
gt;gt;gt; View this thread:
gt;gt;gt; www.excelforum.com/showthread...hreadid=513151
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
all sorted now thank you for everyones help--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=513151thanks understood.

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; You need to define a name as ColumnA, I am assuming Bob used this just
gt; because the OP used
gt; it, I am sure the OP is using something else, however to get it to work as
gt; written you need to define a name for the ranges you are using
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;R..VENKATARAMANquot; gt; wrote in message
gt; ...
gt;gt;I have problem If I use columnA in the formula it gives error--#NAME
gt;gt; If I use actual range for e.g. A1:A10 instead of columnA it works.
gt;gt; mine is XP/excel2002. What mistake am I doing?
gt;gt;
gt;gt;
gt;gt; quot;Bob Phillipsquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; =sumproduct(--(columnA={quot;Greenquot;,quot;Bluequot;}))
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; HTH
gt;gt;gt;
gt;gt;gt; Bob Phillips
gt;gt;gt;
gt;gt;gt; (remove nothere from email address if mailing direct)
gt;gt;gt;
gt;gt;gt; quot;ceemoquot; gt; wrote in
gt;gt;gt; message ...
gt;gt;gt;gt;
gt;gt;gt;gt; Ive started using the sumproduct function to count multiple conditions
gt;gt;gt;gt; which is useful
gt;gt;gt;gt;
gt;gt;gt;gt; howveer if i want to count those records in one column that meet a
gt;gt;gt;gt; condition and those records in another column that meet anyone of a
gt;gt;gt;gt; number of conditions how can i do that?
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; the only way i can think is like the below
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; =sumproduct(--((columnA=apple)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; Rather than having to eliminate red and yellow i would like to say is
gt;gt;gt;gt; green or blue.
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; Please help
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt; ceemo
gt;gt;gt;gt; ------------------------------------------------------------------------
gt;gt;gt;gt; ceemo's Profile:
gt;gt;gt; www.excelforum.com/member.php...oamp;userid=10650
gt;gt;gt;gt; View this thread:
gt;gt;gt;gt; www.excelforum.com/showthread...hreadid=513151
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
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)

quot;R..VENKATARAMANquot; gt; wrote in message
...
gt; I have problem If I use columnA in the formula it gives error--#NAME
gt; If I use actual range for e.g. A1:A10 instead of columnA it works.
gt; mine is XP/excel2002. What mistake am I doing?
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; =sumproduct(--(columnA={quot;Greenquot;,quot;Bluequot;}))
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;ceemoquot; gt; wrote in
gt; gt; message ...
gt; gt;gt;
gt; gt;gt; Ive started using the sumproduct function to count multiple conditions
gt; gt;gt; which is useful
gt; gt;gt;
gt; gt;gt; howveer if i want to count those records in one column that meet a
gt; gt;gt; condition and those records in another column that meet anyone of a
gt; gt;gt; number of conditions how can i do that?
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; the only way i can think is like the below
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; =sumproduct(--((columnA=apple)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Rather than having to eliminate red and yellow i would like to say is
gt; gt;gt; green or blue.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Please help
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; ceemo
gt;
gt;gt; ------------------------------------------------------------------------
gt; gt;gt; ceemo's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=10650
gt; gt;gt; View this thread:
gt; gt;gt; www.excelforum.com/showthread...hreadid=513151
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
thank you Mr. phillips for caring to to clarify to me.quot;Bob Phillipsquot; gt; wrote in message
...
gt; Yeah sorry about not being clear there, as Peo says, I answered in the
gt; OP's
gt; style as he had got SUMPRODUCT working, so I assumed that his columnA was
gt; a
gt; range within column A.
gt;
gt; SUMPRODUCT doesn't work with complete columns, you have to specify a
gt; range.
gt; You can get close, like A1:A65535, but not A:A.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;R..VENKATARAMANquot; gt; wrote in message
gt; ...
gt;gt; I have problem If I use columnA in the formula it gives error--#NAME
gt;gt; If I use actual range for e.g. A1:A10 instead of columnA it works.
gt;gt; mine is XP/excel2002. What mistake am I doing?
gt;gt;
gt;gt;
gt;gt; quot;Bob Phillipsquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; =sumproduct(--(columnA={quot;Greenquot;,quot;Bluequot;}))
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; HTH
gt;gt; gt;
gt;gt; gt; Bob Phillips
gt;gt; gt;
gt;gt; gt; (remove nothere from email address if mailing direct)
gt;gt; gt;
gt;gt; gt; quot;ceemoquot; gt; wrote in
gt;gt; gt; message ...
gt;gt; gt;gt;
gt;gt; gt;gt; Ive started using the sumproduct function to count multiple conditions
gt;gt; gt;gt; which is useful
gt;gt; gt;gt;
gt;gt; gt;gt; howveer if i want to count those records in one column that meet a
gt;gt; gt;gt; condition and those records in another column that meet anyone of a
gt;gt; gt;gt; number of conditions how can i do that?
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; the only way i can think is like the below
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; =sumproduct(--((columnA=apple)*((ColumnBlt;gt;Red)*(columnBlt;gt;Yellow) )))
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Rather than having to eliminate red and yellow i would like to say is
gt;gt; gt;gt; green or blue.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Please help
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; ceemo
gt;gt;
gt;gt;gt; ------------------------------------------------------------------------
gt;gt; gt;gt; ceemo's Profile:
gt;gt; gt; www.excelforum.com/member.php...oamp;userid=10650
gt;gt; gt;gt; View this thread:
gt;gt; gt;gt; www.excelforum.com/showthread...hreadid=513151
gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;