Date count

I have a spreadsheet with dates in the cells.
I want to create a formula that looks at column A and column B, if they are
blank... then look at column C and if it is blank count it as 1. I tried
something like this.. but it didn't give me the answer I was looking for!

=SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1)))

It seems like if A7:B7 are blank....count that as 1

If that's true, try this:
=--(COUNTA(A7:C7)=0)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
news:17972F74-1835-4F5F-A486-759289AB5EFB@microsoft.com...
>I have a spreadsheet with dates in the cells.
> I want to create a formula that looks at column A and column B, if they
> are
> blank... then look at column C and if it is blank count it as 1. I tried
> something like this.. but it didn't give me the answer I was looking for!
>
> =SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1)))

I *meant* to say:
"It seems like if A7:C7 are blank....count that as 1"
not A7:B7

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
news:OUIsOx5gIHA.1944@TK2MSFTNGP02.phx.gbl...
> It seems like if A7:B7 are blank....count that as 1
>
> If that's true, try this:
> =--(COUNTA(A7:C7)=0)
>
> Is that something you can work with?
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> "tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
> news:17972F74-1835-4F5F-A486-759289AB5EFB@microsoft.com...
>>I have a spreadsheet with dates in the cells.
>> I want to create a formula that looks at column A and column B, if they
>> are
>> blank... then look at column C and if it is blank count it as 1. I tried
>> something like this.. but it didn't give me the answer I was looking for!
>>
>> =SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1)))
>
>

Thank you!
That worked great!
Ya’ll make it seem so simple!
What about a formula that looks at B and C, if they are blank, then looks
and A and if column A has a date – count it as “1”.


"Ron Coderre" wrote:

> I *meant* to say:
> "It seems like if A7:C7 are blank....count that as 1"
> not A7:B7
>
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> "Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
> news:OUIsOx5gIHA.1944@TK2MSFTNGP02.phx.gbl...
> > It seems like if A7:B7 are blank....count that as 1
> >
> > If that's true, try this:
> > =--(COUNTA(A7:C7)=0)
> >
> > Is that something you can work with?
> > --------------------------
> >
> > Regards,
> >
> > Ron
> > Microsoft MVP (Excel)
> > (XL2003, Win XP)
> >
> > "tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
> > news:17972F74-1835-4F5F-A486-759289AB5EFB@microsoft.com...
> >>I have a spreadsheet with dates in the cells.
> >> I want to create a formula that looks at column A and column B, if they
> >> are
> >> blank... then look at column C and if it is blank count it as 1. I tried
> >> something like this.. but it didn't give me the answer I was looking for!
> >>
> >> =SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1)))
> >
> >
>
>
>

OK...counting "dates" is harder than you might think.
To Excel, each date is just a number representing the
number of days that date is from 31-DEC-1899.

01-JAN-1900 is 1
11-MAR-2008 is 39,518

However, Excel can conveniently format those numbers
properly so they make sense to humans.

How about if we just test if Col_A is numeric if B:C is blank.
Is that close enough?
=IF(COUNTA(B7:C7),0,--ISNUMBER(A7))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
news:96AA75BB-44F9-4B60-9E2E-8C842C387122@microsoft.com...
> Thank you!
> That worked great!
> Ya'll make it seem so simple!
> What about a formula that looks at B and C, if they are blank, then looks
> and A and if column A has a date - count it as "1".
>
>
> "Ron Coderre" wrote:
>
>> I *meant* to say:
>> "It seems like if A7:C7 are blank....count that as 1"
>> not A7:B7
>>
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>> "Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
>> news:OUIsOx5gIHA.1944@TK2MSFTNGP02.phx.gbl...
>> > It seems like if A7:B7 are blank....count that as 1
>> >
>> > If that's true, try this:
>> > =--(COUNTA(A7:C7)=0)
>> >
>> > Is that something you can work with?
>> > --------------------------
>> >
>> > Regards,
>> >
>> > Ron
>> > Microsoft MVP (Excel)
>> > (XL2003, Win XP)
>> >
>> > "tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
>> > news:17972F74-1835-4F5F-A486-759289AB5EFB@microsoft.com...
>> >>I have a spreadsheet with dates in the cells.
>> >> I want to create a formula that looks at column A and column B, if
>> >> they
>> >> are
>> >> blank... then look at column C and if it is blank count it as 1. I
>> >> tried
>> >> something like this.. but it didn't give me the answer I was looking
>> >> for!
>> >>
>> >> =SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1)))
>> >
>> >
>>
>>
>>

Just had to say thank you again! worked like a charm!

"Ron Coderre" wrote:

> OK...counting "dates" is harder than you might think.
> To Excel, each date is just a number representing the
> number of days that date is from 31-DEC-1899.
>
> 01-JAN-1900 is 1
> 11-MAR-2008 is 39,518
>
> However, Excel can conveniently format those numbers
> properly so they make sense to humans.
>
> How about if we just test if Col_A is numeric if B:C is blank.
> Is that close enough?
> =IF(COUNTA(B7:C7),0,--ISNUMBER(A7))
>
> Is that something you can work with?
> Post back if you have more questions.
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
>
>
> "tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
> news:96AA75BB-44F9-4B60-9E2E-8C842C387122@microsoft.com...
> > Thank you!
> > That worked great!
> > Ya'll make it seem so simple!
> > What about a formula that looks at B and C, if they are blank, then looks
> > and A and if column A has a date - count it as "1".
> >
> >
> > "Ron Coderre" wrote:
> >
> >> I *meant* to say:
> >> "It seems like if A7:C7 are blank....count that as 1"
> >> not A7:B7
> >>
> >> --------------------------
> >>
> >> Regards,
> >>
> >> Ron
> >> Microsoft MVP (Excel)
> >> (XL2003, Win XP)
> >>
> >> "Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
> >> news:OUIsOx5gIHA.1944@TK2MSFTNGP02.phx.gbl...
> >> > It seems like if A7:B7 are blank....count that as 1
> >> >
> >> > If that's true, try this:
> >> > =--(COUNTA(A7:C7)=0)
> >> >
> >> > Is that something you can work with?
> >> > --------------------------
> >> >
> >> > Regards,
> >> >
> >> > Ron
> >> > Microsoft MVP (Excel)
> >> > (XL2003, Win XP)
> >> >
> >> > "tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
> >> > news:17972F74-1835-4F5F-A486-759289AB5EFB@microsoft.com...
> >> >>I have a spreadsheet with dates in the cells.
> >> >> I want to create a formula that looks at column A and column B, if
> >> >> they
> >> >> are
> >> >> blank... then look at column C and if it is blank count it as 1. I
> >> >> tried
> >> >> something like this.. but it didn't give me the answer I was looking
> >> >> for!
> >> >>
> >> >> =SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1)))
> >> >
> >> >
> >>
> >>
> >>
>
>
>
>

Glad to help.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
news:A0CF1DD2-209A-476C-8E75-9E63079A10F1@microsoft.com...
> Just had to say thank you again! worked like a charm!
>
> "Ron Coderre" wrote:
>
>> OK...counting "dates" is harder than you might think.
>> To Excel, each date is just a number representing the
>> number of days that date is from 31-DEC-1899.
>>
>> 01-JAN-1900 is 1
>> 11-MAR-2008 is 39,518
>>
>> However, Excel can conveniently format those numbers
>> properly so they make sense to humans.
>>
>> How about if we just test if Col_A is numeric if B:C is blank.
>> Is that close enough?
>> =IF(COUNTA(B7:C7),0,--ISNUMBER(A7))
>>
>> Is that something you can work with?
>> Post back if you have more questions.
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>>
>>
>> "tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
>> news:96AA75BB-44F9-4B60-9E2E-8C842C387122@microsoft.com...
>> > Thank you!
>> > That worked great!
>> > Ya'll make it seem so simple!
>> > What about a formula that looks at B and C, if they are blank, then
>> > looks
>> > and A and if column A has a date - count it as "1".
>> >
>> >
>> > "Ron Coderre" wrote:
>> >
>> >> I *meant* to say:
>> >> "It seems like if A7:C7 are blank....count that as 1"
>> >> not A7:B7
>> >>
>> >> --------------------------
>> >>
>> >> Regards,
>> >>
>> >> Ron
>> >> Microsoft MVP (Excel)
>> >> (XL2003, Win XP)
>> >>
>> >> "Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
>> >> news:OUIsOx5gIHA.1944@TK2MSFTNGP02.phx.gbl...
>> >> > It seems like if A7:B7 are blank....count that as 1
>> >> >
>> >> > If that's true, try this:
>> >> > =--(COUNTA(A7:C7)=0)
>> >> >
>> >> > Is that something you can work with?
>> >> > --------------------------
>> >> >
>> >> > Regards,
>> >> >
>> >> > Ron
>> >> > Microsoft MVP (Excel)
>> >> > (XL2003, Win XP)
>> >> >
>> >> > "tkacoo" <tkacoo@discussions.microsoft.com> wrote in message
>> >> > news:17972F74-1835-4F5F-A486-759289AB5EFB@microsoft.com...
>> >> >>I have a spreadsheet with dates in the cells.
>> >> >> I want to create a formula that looks at column A and column B, if
>> >> >> they
>> >> >> are
>> >> >> blank... then look at column C and if it is blank count it as 1. I
>> >> >> tried
>> >> >> something like this.. but it didn't give me the answer I was
>> >> >> looking
>> >> >> for!
>> >> >>
>> >> >> =SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1)))
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
>>