Thank you for your suggestions.

I am very much aware that SUM plus CSE and SUM(IF( )) plus CSE are equivalent to SUMPRODUCT. However, in this example, we want to count; so I was "fixated" on COUNT and COUNT(IF( )) and I got stuck. I have another question and I am going to start my own thread "SUM, COUNT and SUMPRODUCT?"

Brandoni, hope you won't mind my question. Sometimes, I find it easier for all readers to group all formulae together.
Have you found your favourite formula out of the choices provided? I wonder if anyone else has another suggestion. We'll wait and see.

Epinn

"Teethless mama" wrote in message ...
No need a helper column. There are many way to count without using SUMPRODUCT
Function.

=SUM((A1:A100="a")*(B1:B100="J"))
or
=SUM(IF((A1:A100="a")*(B1:B100="J"),1))
or
=SUM(IF(A1:A100="a",IF(B1:B100="J",1)))


All of these above are array formula you have to commit Ctrl Shift enter
(not just enter)

"Epinn" wrote:

Yes, I would use SUMPRODUCT and my formula is the same as Trevor's except for the ranges.

I am curious if we don't use SUMPRODUCT, how would we do it, say with COUNT, IF, AND etc.?

The only alternative I can think of is to use COUNTIF on a helper column (e.g. H) containing this formula =AND(A1="a",E1="j") (copied down the column). Then use the following formula:

=COUNTIF(H1:H10,TRUE)

TIA

Epinn

"Trevor Shuttleworth" wrote in message ...
=SUMPRODUCT((A1:A51="a")*(E1:E51="J"))

Regards

Trevor


"Brandoni" wrote in message
...
Basically have a spreadsheet of info on current jobs. On sheet 2 i would
like a count of active jobs.

On Sheet 1 data is laid out as follows:
Column A = "a" or blank
Column E = "J" or "S" or blank

The problem is that old jobs are also in the sheet. So countif(E:E,"J")
returns all the j's even if the job is no longer active (Column A is
blank,
but column E has a "J").

Formula needed is: If Col A="a" and column E="J" then count.

Basically want to find number of Js when column A ="a". I can use Countif
for number of "a'"s but can't figure out how to condition a formula to
count
the "j" in only the rows with a corresponding "a" in column A.

Thank you for your help!