Count formula

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

 

[Max]

In C100: =SUMPRODUCT((A1:A100="ON")*(B1:B100<>""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[muddan madhu]

Array function ( use ctrl + shift + enter )

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

 

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

 

[Max]

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

 

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