Count/Sum data with date entries.
 

I'm trying to count/sum the number of rows that have multiple date
entries and based on certain criteria. I've been working with
"Counta", "Sum" & "IF/AND" functions.

Example:
There's a date entry in cells of Column A, C & F. I want to first
check or identify if date is in A or C, then check if there's a date in
F. If both are true, then return "0", if just A or C is true, then
return "1", and if just F is true, then return "0".

This is the latest function I was working with:
{=SUM((IF(A26VALUE("1/1/05")+(C26VALUE("1/1/05")),1)*(F26="")))}

Thx for any help,
Ken


--
KBW
------------------------------------------------------------------------
KBW's Profile: http://www.excelforum.com/member.php...o&userid=32730
View this thread: http://www.excelforum.com/showthread...hreadid=525663

Count/Sum data with date entries.
 

Hi!

Are you checking for a specific date OR any date? This will work for ANY
date.

You didn't define what to do if none of the cells has a date so this formula
will leave the cell blank for that condition:

=IF(AND(COUNT(A26,C26),COUNT(F26)),0,IF(COUNT(A26, C26),1,IF(COUNT(F26),0,"")))

Biff

"KBW" wrote in message
...

I'm trying to count/sum the number of rows that have multiple date
entries and based on certain criteria. I've been working with
"Counta", "Sum" & "IF/AND" functions.

Example:
There's a date entry in cells of Column A, C & F. I want to first
check or identify if date is in A or C, then check if there's a date in
F. If both are true, then return "0", if just A or C is true, then
return "1", and if just F is true, then return "0".

This is the latest function I was working with:
{=SUM((IF(A26VALUE("1/1/05")+(C26VALUE("1/1/05")),1)*(F26="")))}

Thx for any help,
Ken


--
KBW
------------------------------------------------------------------------
KBW's Profile:
http://www.excelforum.com/member.php...o&userid=32730
View this thread: http://www.excelforum.com/showthread...hreadid=525663

Count/Sum data with date entries.
 

Thx Biff,

Worked perfectly, yes, I just needed to validate any date in the cell.
I appreciate the help!

Ken


--
KBW
------------------------------------------------------------------------
KBW's Profile: http://www.excelforum.com/member.php...o&userid=32730
View this thread: http://www.excelforum.com/showthread...hreadid=525663

Count/Sum data with date entries.
 

You're welcome. Thanks for the feedback!

Biff

"KBW" wrote in message
...

Thx Biff,

Worked perfectly, yes, I just needed to validate any date in the cell.
I appreciate the help!

Ken


--
KBW
------------------------------------------------------------------------
KBW's Profile:
http://www.excelforum.com/member.php...o&userid=32730
View this thread: http://www.excelforum.com/showthread...hreadid=525663

Count/Sum data with date entries.
 

Hey Biff,

Please read my last message, and I'm trying to also sum the total row
counts into one cell. So if I were to have say 100 rows with 25 of
them having "1", then this single cell would display "25".

thx,
Ken


--
KBW
------------------------------------------------------------------------
KBW's Profile: http://www.excelforum.com/member.php...o&userid=32730
View this thread: http://www.excelforum.com/showthread...hreadid=525663

Count/Sum data with date entries.
 

Hi!

Not really following you on this one.

So, do you want to count all the cells in which the formula returns a 1?

One of these will do:

=SUM(A1:A100)

=COUNTIF(A1:A100,1)

Biff

"KBW" wrote in message
...

Hey Biff,

Please read my last message, and I'm trying to also sum the total row
counts into one cell. So if I were to have say 100 rows with 25 of
them having "1", then this single cell would display "25".

thx,
Ken


--
KBW
------------------------------------------------------------------------
KBW's Profile:
http://www.excelforum.com/member.php...o&userid=32730
View this thread: http://www.excelforum.com/showthread...hreadid=525663

Count/Sum data with date entries.
 

If I go back to my original posting & question, I'm trying to go down 3
columns and depending on the sequence of dates entered, the formula
will return a "1" or "0".

Your formula works when associated to each row going down the columns.
I'm trying to have a formula in a single cell do the date validation
above, then sum/count the total "1" and this would be the result.

thx,
ken


--
KBW
------------------------------------------------------------------------
KBW's Profile: http://www.excelforum.com/member.php...o&userid=32730
View this thread: http://www.excelforum.com/showthread...hreadid=525663