COUNTIF Function Formula Question
 

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

COUNTIF Function Formula Question
 

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" wrote in message
...
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

COUNTIF Function Formula Question
 

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")*(ISNUMBE R(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


"Bonnie" wrote:

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

COUNTIF Function Formula Question
 

Hi,

Try this

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

F55:F56 contains WW and SS

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Bonnie" wrote in message
...
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

COUNTIF Function Formula Question
 

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

If this post helps click Yes
---------------
Jacob Skaria


"Bonnie" wrote:

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

COUNTIF Function Formula Question
 

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

On Nov 3, 3:52*pm, "Peo Sjoblom" wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

COUNTIF Function Formula Question
 

On Nov 3, 4:25*pm, Mike H wrote:
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")*(ISNUMB ER(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



"Bonnie" wrote:
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- Hide quoted text -

- 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.

COUNTIF Function Formula Question
 

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


"Bonnie" wrote in message
...
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

On Nov 3, 3:52 pm, "Peo Sjoblom" wrote:
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" wrote in message

...



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- Hide
quoted text -

- Show quoted text -