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!

=SUMPRODUCT((Sheet1!A1:A1000="a")*(Sheet1!E1:E1000 ="J"))



"Brandoni" skrev:

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!