# COUNTIF Function Formula Question

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

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

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

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.

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