software

how do i get excel to give me the sum if two criteria are true, but i want it
to return a value of only one of the criteria. For example, column A is the
text 1A, column B is number 1. i need a formula that says if test in column
A=quot;1Aquot; AND column B=quot;1quot;, then return the total number of times that quot;1Aquot;
appears in column A.

Something like:

=SUMPRODUCT((A1:A40=quot;1aquot;)*(B1:B40=1))quot;blopreste3180quot; gt; wrote in message
...
gt; how do i get excel to give me the sum if two criteria are true, but i want
gt; it
gt; to return a value of only one of the criteria. For example, column A is
gt; the
gt; text 1A, column B is number 1. i need a formula that says if test in
gt; column
gt; A=quot;1Aquot; AND column B=quot;1quot;, then return the total number of times that quot;1Aquot;
gt; appears in column A.
Hi,

Try the following array formula (Ctrl Shift Enter)

=count(if((range1=quot;1Aquot;)*(range2=1),range1))

Regards,

Ashish Mathur

quot;blopreste3180quot; wrote:

gt; how do i get excel to give me the sum if two criteria are true, but i want it
gt; to return a value of only one of the criteria. For example, column A is the
gt; text 1A, column B is number 1. i need a formula that says if test in column
gt; A=quot;1Aquot; AND column B=quot;1quot;, then return the total number of times that quot;1Aquot;
gt; appears in column A.

This did not work. It returned a value of quot;0quot; (zero). The formula should
reference my first range of B3:B3001 which, for example, will =quot;1quot; and my
second range of GS3:GS3001, must also =quot;1quot;, then I need the sum of ONLY the
instance of quot;1quot; in B3:B3001 if BOTH of these criteria are met, otherwise,
return Blank or zero.

quot;Ashish Mathurquot; wrote:

gt; Hi,
gt;
gt; Try the following array formula (Ctrl Shift Enter)
gt;
gt; =count(if((range1=quot;1Aquot;)*(range2=1),range1))
gt;
gt; Regards,
gt;
gt; Ashish Mathur
gt;
gt; quot;blopreste3180quot; wrote:
gt;
gt; gt; how do i get excel to give me the sum if two criteria are true, but i want it
gt; gt; to return a value of only one of the criteria. For example, column A is the
gt; gt; text 1A, column B is number 1. i need a formula that says if test in column
gt; gt; A=quot;1Aquot; AND column B=quot;1quot;, then return the total number of times that quot;1Aquot;
gt; gt; appears in column A.