software

To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
Thanks
will A

Try this:
=SUMPRODUCT(--(A1:A10=quot;aaquot;),--(B1:B10=quot;bbquot;),--(C1:C10=quot;ccquot;))

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ronquot;will Aquot; wrote:

gt; To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
gt; Thanks
gt; will A

=SUMPRODUCT(--(A1:A1000=quot;aaquot;),--(B1:B1000=quot;bbquot;),--(C1:c1000=quot;ccquot;))

SUMPRODUCT does not work on a complete column, just a defined range, and all
ranges must be the same size

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;will Aquot; gt; wrote in message
...
gt; To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
gt; Thanks
gt; will A
Will define the range and try it. Thanks Bob.

quot;Bob Phillipsquot; wrote:

gt; =SUMPRODUCT(--(A1:A1000=quot;aaquot;),--(B1:B1000=quot;bbquot;),--(C1:c1000=quot;ccquot;))
gt;
gt; SUMPRODUCT does not work on a complete column, just a defined range, and all
gt; ranges must be the same size
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;will Aquot; gt; wrote in message
gt; ...
gt; gt; To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
gt; gt; Thanks
gt; gt; will A
gt;
gt;
gt;

Thanks will try it Ron.

quot;Ron Coderrequot; wrote:

gt; Try this:
gt; =SUMPRODUCT(--(A1:A10=quot;aaquot;),--(B1:B10=quot;bbquot;),--(C1:C10=quot;ccquot;))
gt;
gt; Adjust range references to suit your situation
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt;
gt; quot;will Aquot; wrote:
gt;
gt; gt; To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
gt; gt; Thanks
gt; gt; will A

Im getting 0 (zero). what do you mean adjust range reference? thanks

quot;Ron Coderrequot; wrote:

gt; Try this:
gt; =SUMPRODUCT(--(A1:A10=quot;aaquot;),--(B1:B10=quot;bbquot;),--(C1:C10=quot;ccquot;))
gt;
gt; Adjust range references to suit your situation
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt;
gt; quot;will Aquot; wrote:
gt;
gt; gt; To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
gt; gt; Thanks
gt; gt; will A

This solved my problem as well but how does this work? what does the -- do?

quot;Bob Phillipsquot; wrote:

gt; =SUMPRODUCT(--(A1:A1000=quot;aaquot;),--(B1:B1000=quot;bbquot;),--(C1:c1000=quot;ccquot;))
gt;
gt; SUMPRODUCT does not work on a complete column, just a defined range, and all
gt; ranges must be the same size
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;will Aquot; gt; wrote in message
gt; ...
gt; gt; To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
gt; gt; Thanks
gt; gt; will A
gt;
gt;
gt;

This would work just as well, and do the same job:

=SUMPRODUCT((A1:A1000=quot;aaquot;)*(B1:B1000=quot;bbquot;)*(C1:c1 000=quot;ccquot;))

See Bob's web page on the subject to find out how and why.

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Dan Shoemakerquot; lt;Dan gt; wrote in message
...
gt; This solved my problem as well but how does this work? what does the --
do?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--(A1:A1000=quot;aaquot;),--(B1:B1000=quot;bbquot;),--(C1:c1000=quot;ccquot;))
gt; gt;
gt; gt; SUMPRODUCT does not work on a complete column, just a defined range, and
all
gt; gt; ranges must be the same size
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;will Aquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; To count if Col A=aa and Col B=bb and Col C=cc. Must meet all
situation.
gt; gt; gt; Thanks
gt; gt; gt; will A
gt; gt;
gt; gt;
gt; gt;I am using the formula =SUMPRODUCT(--(B2:B12=quot;Active*quot;),--(E2:E12=quot;*quot;)) to
obtain a total count but receive 0.
I want to count where entries in range B = quot;Activequot; and entries in range E
is not null. HELP?!?!

B C D E
2 ActiveAC745867AC745867AC745867
3 ActiveAD041297AD041297
4 ActiveAD041298AD041298AD041298
5 ActiveAD041299AD041299AD041299
6 ActiveAD041300AD041300AD041300
7 ActiveAD041313AD041313
8 ActiveAD041312AD041312AD041312
9 ActiveAC290419AC290419AC290419
10 ActiveAC747441AC747441AC747441
11 ActiveAD041302AD041302AD041302
12 ActiveAD041311AD041311=SUMPRODUCT(--(B2:B12=quot;Activequot;),--(E2:E12lt;gt;quot;quot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Timquot; gt; wrote in message
...
gt; I am using the formula =SUMPRODUCT(--(B2:B12=quot;Active*quot;),--(E2:E12=quot;*quot;)) to
gt; obtain a total count but receive 0.
gt; I want to count where entries in range B = quot;Activequot; and entries in range E
gt; is not null. HELP?!?!
gt;
gt; B C D E
gt; 2 Active AC745867 AC745867 AC745867
gt; 3 Active AD041297 AD041297
gt; 4 Active AD041298 AD041298 AD041298
gt; 5 Active AD041299 AD041299 AD041299
gt; 6 Active AD041300 AD041300 AD041300
gt; 7 Active AD041313 AD041313
gt; 8 Active AD041312 AD041312 AD041312
gt; 9 Active AC290419 AC290419 AC290419
gt; 10 Active AC747441 AC747441 AC747441
gt; 11 Active AD041302 AD041302 AD041302
gt; 12 Active AD041311 AD041311
gt;