return value based on two criteria

[Guest]

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="1A" AND column B="1", then return the total number of times that "1A"
appears in column A.

 

[Vasant Nanavati]

Something like:

=SUMPRODUCT((A1:A40="1a")*(B1:B40=1))

 

[Guest]

Hi,

Try the following array formula (Ctrl+Shift+Enter)

=count(if((range1="1A")*(range2=1),range1))

Regards,

Ashish Mathur

 

[Guest]

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