Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
Hi,
I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
{=SUM(IF((WEEKDAY(array1,2)=6),1,0))}
or {=SUM(IF((WEEKDAY(array1,1)=7),1,0))} HTH Kostis Vezerides H wrote: Hi, I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
Try something like this:
With dates in A1:A100 This formula counts the number of Saturdays =SUMPRODUCT(--(WEEKDAY(A1:A100)=7)) Or....if some of the cells may be blank: =SUMPRODUCT(--((A1:A1000)*(WEEKDAY(A1:A100)=7))) Does that help? *********** Regards, Ron XL2002, WinXP "H" wrote: Hi, I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
it looks you're aware of entering an array formula
lets see another idea maybe something like this =SUM(--(WEEKDAY(A1:A20)=7)) hit ctrl-shft-ent.. -- ***** birds of the same feather flock together.. "H" wrote: Hi, I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
=SUMPRODUCT(--(WEEKDAY(A1:A20)6))
Biff "H" wrote in message ... Hi, I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
And one mo
=SUM(IF(MOD(R,7),,1)) Array entered. Where R is a range name for your data. -- Thanks, Shane Devenshire "T. Valko" wrote: =SUMPRODUCT(--(WEEKDAY(A1:A20)6)) Biff "H" wrote in message ... Hi, I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
I think that
=SUM(IF(MOD(R,7),,1)) is a little dangerous, Shane. If you're using the 1904 date system it'll count Fridays....... "ShaneDevenshire" wrote: And one mo =SUM(IF(MOD(R,7),,1)) Array entered. Where R is a range name for your data. -- Thanks, Shane Devenshire "T. Valko" wrote: =SUMPRODUCT(--(WEEKDAY(A1:A20)6)) Biff "H" wrote in message ... Hi, I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Saturdays in a List
You can modify it to work with the 1904 date system. I don't suspect that a
lot of people on PC's use that? And maybe not even that many on the Mac. =SUM(IF(MOD(R,7)=1,1)) -- Cheers, Shane Devenshire "daddylonglegs" wrote: I think that =SUM(IF(MOD(R,7),,1)) is a little dangerous, Shane. If you're using the 1904 date system it'll count Fridays....... "ShaneDevenshire" wrote: And one mo =SUM(IF(MOD(R,7),,1)) Array entered. Where R is a range name for your data. -- Thanks, Shane Devenshire "T. Valko" wrote: =SUMPRODUCT(--(WEEKDAY(A1:A20)6)) Biff "H" wrote in message ... Hi, I need to count the number of Saturdays in an array of dates. I used the following approach, which didn't work. Does anyone have another idea? {=SUM(IF((WEEKDAY(array1,2)=7),1,0))} Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count the frequency of items in a non-numeric list? | Excel Worksheet Functions | |||
How do I get a cell to count values from a list dependent on anoth | Excel Worksheet Functions | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
Setting for Excel automatically show count result in list ? | Excel Worksheet Functions | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) |