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!