COUNTIF Function Formula Question

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

May I have help with this formula, please. I need to count how many times
"WW" or"SS" are entered in Column F ... AND ... there is a date entered in
Column O on the same Row as the WW or SS appear in Column A.

I got this far: =COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS") ... but
I don't know how to include the reference to Column O.

By the way, yes, the formula I'm creating is on a separate worksheet than
the reference cells.

Thank you, in advance, for any guidance you can give. Bonnie
 
Try something like


=SUMPRODUCT((F2:F30={"WW","SS"})*(ISNUMBER(O2:O30)))


note that unless you have 2007 you would need to specify the range you are
using, also in your example you used F:F and not A:A so I used F as well
whereas you wrote that you were testing column A

--


Regards,


Peo Sjoblom
 
Bonnie,

Try this but because you are using full columns it will be slow. I would
recommend you shorten the range to something more practical.

=SUMPRODUCT((Master!F:F="WW")*(ISNUMBER(Master!O:O)))+SUMPRODUCT((Master!F:F="SS")*(ISNUMBER(Master!O:O)))

with shortened ranges

=SUMPRODUCT((Master!F1:F25="WW")*(ISNUMBER(Master!O1:O25)))+SUMPRODUCT((Master!F1:F25="SS")*(ISNUMBER(Master!O1:O25)))

Another point to note is there is no ISDATE worksheet function in Excel so
this looks for a number on col O

Mike
 
Try
=SUMPRODUCT((ISNUMBER(MATCH(F1:F100,{"WW","SS"},0)))*
(ISNUMBER(O1:O100)))

If this post helps click Yes
 
Thank you. But, there are some funny brackets arount the "WW","SS";
is this an array formula? When I typed it in I held down the CTRL and
ALT but I didn't get the brackets as shown in the formula you wrote
out. Please advise.

Bonnie
 
Bonnie,

Try this but because you are using full columns it will be slow. I would
recommend you shorten the range to something more practical.

=SUMPRODUCT((Master!F:F="WW")*(ISNUMBER(Master!O:O)))+SUMPRODUCT((Master!F:­F="SS")*(ISNUMBER(Master!O:O)))

with shortened ranges

=SUMPRODUCT((Master!F1:F25="WW")*(ISNUMBER(Master!O1:O25)))+SUMPRODUCT((Mas­ter!F1:F25="SS")*(ISNUMBER(Master!O1:O25)))

Another point to note is there is no ISDATE worksheet function in Excel so
this looks for a number on col O

Mike








- Show quoted text -
Click to expand...

This is apparently the formula to use. Three people suggestioned the
same or similar type formulas. Thank you very much for your reply.
 
No the brackets are entered physically, no array formula entering and given
your conditions the formula works without taking a whole lot of space

--


Regards,


Peo Sjoblom


Thank you. But, there are some funny brackets arount the "WW","SS";
is this an array formula? When I typed it in I held down the CTRL and
ALT but I didn't get the brackets as shown in the formula you wrote
out. Please advise.

Bonnie
 
Back
Top