| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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))) |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Date count
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" wrote in message ... 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))) |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Date count
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" wrote in message ... 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" wrote in message ... 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))) |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Date count
Thank you!
That worked great! Yall 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" wrote in message ... 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" wrote in message ... 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))) |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Date count
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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))) |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Date count
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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))) |
|
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Date count
Glad to help.
-------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "tkacoo" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
| count the mean in few date but on de date it has various data | Excel Worksheet Functions | |||
| count between start date and end date | Excel Discussion (Misc queries) | |||
| Count number of cells with date <today's date | New Users to Excel | |||
| count the number of cells with a date <= today's date | New Users to Excel | |||
Linear Mode
