Excel - Count formula

Asked By niko on 02-Jan-09 01:41 AM
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.




demechani replied on 02-Jan-09 02:06 AM
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 replied on 08-Jan-09 05:00 AM
Array function ( use ctrl + shift + enter )

=3DCOUNT(IF((A1:A14=3D"on")*(B1:B14>0),))



dates and empty
=94
ns:
.
niko replied on 02-Jan-09 03:13 AM
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.
demechani replied on 02-Jan-09 03:41 AM
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.


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
---
ShaneDevenshir replied on 02-Jan-09 04:17 PM
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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire