Count/Sum data with date entries.

[KBW]

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

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

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

Thx for any help,
Ke

 

[Terry Gregg]

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(A26>VALUE("1/1/05")+(C26>VALUE("1/1/05")),1)*(F26="")))}

Thx for any help,
Ken

Ken
Try the formula below:
=IF(AND(OR(A26>38352,C26>38352),F26>38352),0,IF(OR(A26>38352,C26>38352),1,IF(F26>38352,0,"")))
this assumes you are testing for a date > 1/1/05, you would need to
change the 38352 if the date you are testing for is different.
Terry

 

[Biff]

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]

Thx Biff,

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

Ken

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

 

[KBW]

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

 

[Biff]

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]

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