COUNTIF Function Formula Question

[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

 

[Peo Sjoblom]

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

 

[Mike H]

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

 

[Ashish Mathur]

Hi,

Try this

=sumproduct((isnumber(match(F2:F50,F55:F56,0)))*isnumber(O2:O50))

F55:F56 contains WW and SS

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Bonnie]

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]

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 -

This is apparently the formula to use. Three people suggestioned the
same or similar type formulas. Thank you very much for your reply.

 

[Peo Sjoblom]

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