Counting two criterias

[jleung07]

So I wanna count the number of cells in A:A that are "A", so like countif(A:A,"A"), then count the number of cells in B:B that are "<1000", so like countif(B:B, "<1000"). How do I combine them both? I am using excel 2003, so I can't use countifs.
I tried using this formula: =SUM(--(A:A="A")*(B:B="<1000")), but it is not working. Thanks!

[TMS]

Try:

=SUMPRODUCT(--($A$2:$A$1000="A"),--($B$2:$B$1000<1000))


Note that you must define ranges, and the size of the ranges must be the same. SUMPRODUCT in Excel 2003 will not accept a full column.


Regards, TMS

[kvsrinivasamurthy]

Pl use this array formula.

{=SUMPRODUCT(--(A:A="A"),--(B:B<1000))}

[Fotis1991]

Hi jleung07 and welcome to the Forum.

1st Formula for this. >>>> =SUM((A1:A10="A")*(B1:B10<=1000))>>Array formula(CSE), so i think is better to use this formula.

=SUMPRODUCT((A1:A10="A")*(B1:B10<=1000))

Hope to helps you.

[jleung07]

Thanks guys

[TMS]

Note that SUMPRODUCT is already an Array formula and does NOT need to be Array Entered with Ctrl-Shift-Enter.

And, as previously stated, SUMPRODUCT in Excel 2003 must have a range not a column.


Regards, TMS

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[kvsrinivasamurthy]

Dear TMshucks,

Thanks for the information.