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

How about an *array* formula using Sum()?
Try this:

=SUM((A1:A100="a")*(E1:E100="J"))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" wrote in message
...
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!