Count formula

N

nikos

Happy new year.

I have two columns (A1:A100 and B1:B100). The first column includes the
values “ONâ€, “OFF†and “-â€. In the second column i have some dates and empty
cells.
I would like to apply a formula in the C100 cell, which counts the “ONâ€
cells in A1:A100 but only if the adjacent cells aren’t empty. (That means:
count all the “ON†in the Ai cells if the Bi cells have dates values).
Any idea?
Thank you.
 
M

muddan madhu

Array function ( use ctrl + shift + enter )

=COUNT(IF((A1:A14="on")*(B1:B14>0),))
 
N

nikos

Thank you both very much.
(If i wanted “…not only the empty cells but only the cells which include
dates ..(not strings, space, etc.)†could be the similar formula?:

C100:= SUMPRODUCT((A1:A100= “ONâ€)*(ISNUMBER(B1:B100)=TRUE))

Thanks again.
 
M

Max

nikos said:
Thank you both very much

Welcome, but pl take a moment to go back and press the YES buttons (like the
ones below, from where you're posting) in ALL responses which helped.
(If i wanted “…not only the empty cells but only the cells which include
dates ..(not strings, space, etc.)†could be the similar formula?:
C100:= SUMPRODUCT((A1:A100= “ONâ€)*(ISNUMBER(B1:B100)=TRUE))

Yes, but its not foolproof since dates are just numbers in Excel,
so any number in col B would also satisfy ISNUMBER

Your formula above could be simplified to just:
=SUMPRODUCT((A1:A100="ON")*(ISNUMBER(B1:B100))

One other way, more robust, is to validate/trap it for a certain date range,
eg: between 1 Jul 2008 to 30 Jun 2009:
=SUMPRODUCT((A1:A100="ON")*(B1:B100>=DATE(2008,7,1))*(B1:B100<DATE(2009,7,1)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
 
S

Shane Devenshire

Hi,

In 2007 you can use

=COUNTIFS(A1:A100,"On",B1:B100,"<>")

In 2003 it is generally safer to you the following form of SUMPRODUCT

=SUMPRODUCT(--(A1:A6="On"),--(B1:B6<>""))

And note that all the suggestions do not actually do what you stated. Why?
Because none of them verify that the entries in column B are date, they just
verify that the cells are not empty.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top