Sixth Day

A

andrew1977

Board Regular
Joined
Dec 3, 2008
Messages
89
Hello All,

I have a list of names in column A and days across row 1, with a tick where each name is working. I need a formula to be able to count how many names are working a sixth day in each week.

Any help would be much appreciated!

Andrew.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Hi Andrew,

Have you considered changing the tick to a "Y" or "Yes" and then do a =Countif(Range,"Y")

Mel
 
Upvote 0
Hi Mel,

I can't use the count function since all sixth days won't always be on the same day (Friday or Saturday), or consecutive for each person. I need a total number of people working a sixth day for every Friday and Saturday.

Hope this makes sense.

Andrew.
 
Upvote 0
<TABLE style="WIDTH: 160pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=211 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" span=7 width=21><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl34 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=64 height=18> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 16pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=21>S</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=21>M</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=21>T</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=21>W</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=21>T</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=21>F</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=21>S</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Name1</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl36 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Name2</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl36 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Name3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl36 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Name4</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl42 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl36 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Name5</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl42 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" x:num>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl37 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name6</TD><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Sixth</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD></TR></TBODY></TABLE>

"Sixth" row is my required formula.

Thanks.
Andrew.
 
Upvote 0
Andrew, I've got to go out but I'll get back to you upon my return.

Mel
 
Upvote 0
Maybe this?
It means some helper columns, so assuming your table is in A1 to H8

In I2 put =IF(COUNT(B2:G2)>=6,"Y","N") and copy down to I7
In J2 put =IF(COUNT(B2:G2)>=6,"Y","N") and copy down to J7
In G8 put =COUNTIF(I2:I7,"Y")
In H8 put =COUNTIF(J2:J7,"Y")

Hope this helps

Woohoohoohooooo

The Tig
 
Upvote 0
Hiya. Unfortuantly I cannot add columns to this particular sheet, I can only put my formula on the bottom row, so I think I need some kind of array formula?

Thanks.
Andrew.
 
Upvote 0
<TABLE dir=ltr borderColor=#051f01 cellSpacing=1 cellPadding=2 width=391 border=1><TBODY><TR><TD width="10%" bgColor=#c0c0c0 height=27>
</TD><TD width="19%" bgColor=#c0c0c0 height=27>
B​
​
​
​
​
</TD><TD width="10%" bgColor=#c0c0c0 height=27>
C​
​
​
​
​
</TD><TD width="10%" bgColor=#c0c0c0 height=27>
D​
​
​
​
​
</TD><TD width="10%" bgColor=#c0c0c0 height=27>
E​
​
​
​
​
</TD><TD width="10%" bgColor=#c0c0c0 height=27>
F​
​
​
​
​
</TD><TD width="10%" bgColor=#c0c0c0 height=27>
G​
​
​
​
​
</TD><TD width="10%" bgColor=#c0c0c0 height=27>
H​
​
​
​
​
</TD><TD width="10%" bgColor=#c0c0c0 height=27>
I​
​
​
​
​
</TD></TR><TR><TD width="10%" bgColor=#c0c0c0 height=27>
2​
​
​
​
​
</TD><TD width="19%" height=27>
</TD><TD width="10%" height=27>
S​
​
​
​
​
</TD><TD width="10%" height=27>
M​
​
​
​
​
</TD><TD width="10%" height=27>
T​
​
​
​
​
</TD><TD width="10%" height=27>
W​
​
​
​
​
</TD><TD width="10%" height=27>
T​
​
​
​
​
</TD><TD width="10%" height=27>
F​
​
​
​
​
</TD><TD width="10%" height=27>
S​
​
​
​
​
</TD></TR><TR><TD width="10%" bgColor=#c0c0c0 height=27>
3​
​
​
​
​
</TD><TD width="19%" height=27>
Name1​
​
​
​
​
</TD><TD width="10%" height=27>
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
</TD></TR><TR><TD width="10%" bgColor=#c0c0c0 height=27>
4​
​
​
​
​
</TD><TD width="19%" height=27>
Name2​
​
​
​
​
</TD><TD width="10%" height=27>
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" bgColor=#ffff00 height=27>
1​
​
​
​
​
</TD></TR><TR><TD width="10%" bgColor=#c0c0c0 height=27>
5​
​
​
​
​
</TD><TD width="19%" height=27>
Name3​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" bgColor=#ffff00 height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
</TD></TR><TR><TD width="10%" bgColor=#c0c0c0 height=27>
6​
​
​
​
​
</TD><TD width="19%" height=27>
Name4​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" bgColor=#ffff00 height=27>
1​
​
​
​
​
</TD></TR><TR><TD width="10%" bgColor=#c0c0c0 height=27>
7​
​
​
​
​
</TD><TD width="19%" height=27>
Name5​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
</TD><TD width="10%" bgColor=#ffff00 height=27>
1​
​
​
​
​
</TD></TR><TR><TD width="10%" bgColor=#c0c0c0 height=27>
8​
​
​
​
​
</TD><TD width="19%" height=27>
Name6​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
</TD><TD width="10%" height=27>
1​
​
​
​
​
</TD><TD width="10%" height=27>
</TD></TR></TBODY></TABLE>

The calculation I'm working on for Friday is -

{=SUM(IF(AND(SUM(C3:H3)=5,H3:H8=1),1,0))}

But I can't seem to make it work it always results zero!? Any help would be much appreciated!

Thanks.
Andrew.
 
Upvote 0
OK Using your table above I was able to get it
sort of working, but with very convoluted formula.

I think your idea of array formula is good but I dont know enough yet to
help with that.

Instead I did this:
In G8 put =SUM(IF(SUM(B2:G2)=6,1,0),IF(SUM(B3:G3)=6,1,0),IF(SUM(B4:G4)=6,1,0),IF(SUM(B5:G5)=6,1,0),IF(SUM(B6:G6)=6,1,0),IF(SUM(B7:G7)=6,1,0))
In H8 put
=SUM(IF(AND(SUM(B2:H2)=6,H2=1),1,0),IF(AND(SUM(B3:H3)=6,H3=1),1,0),IF(AND(SUM(B4:H4)=6,H4=1),1,0),IF(AND(SUM(B5:H5)=6,H5=1),1,0),IF(AND(SUM(B6:H6)=6,H6=1),1,0),IF(AND(SUM(B7:H7)=6,H7=1),1,0))

This works with your table but what happens if there are 7 days in a row? What result do you want? This will increment Friday and Saturday columns.

Hope this helps.

Woohoohoohoooooo!

The Tig
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
409
KRice
K
M
  • Question
Fantasy Calendar Creation stumbling..
Replies
2
Views
128
m26e4u
M
T
  • Question
Tracking sheet date > Due date > days left
Replies
2
Views
255
Drrellik
Drrellik
Z
  • Question
Dynamic Working and Week days remaining until end of month from a specific date in the month
Replies
3
Views
128
Peter_SSs
Peter_SSs
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
265
ashleighbrown
A

Forum statistics

Threads
1,214,653
Messages
6,120,752
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top