how can I count if column A="active" and column E="Job"?

[Guest]

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!

 

[Trevor Shuttleworth]

=SUMPRODUCT((A1:A51="a")*(E1:E51="J"))

Regards

Trevor

 

[Bernard Liengme]

=SUMPRODUCT(((A1:A100="a")+(A1:A100="")),((B1:B100="J")+(B1:B100="S")+(B1:B100=""))
You cannot use whole column (as in A:A) with SUMPRODUCT
best wishes

 

[Epinn]

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

=SUMPRODUCT((A1:A51="a")*(E1:E51="J"))

Regards

Trevor

 

[RagDyer]

<<<"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 !
---------------------------------------------------------------------------
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

=SUMPRODUCT((A1:A51="a")*(E1:E51="J"))

Regards

Trevor

 

[Guest]

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]

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

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)