Do you think you can help with the following excel problem?<o
></o
>
<o
></o
>
I need help with a formula for the number of occurrences at the bottom of the Deposit date column.<o
></o
>
Based upon the entered data the number should equal the number of highlighted cells which is “3”.<o
></o
>
The highlights occur through conditional formatting when the date of deposit is 2 or 3 days greater than the batch date. How can I automate this value so that it will automatically filter for the value of highlighted cells? This data will change monthly so I need the formula to read the highlighted cells only whenever they may occur which could be in any cell. Thanks in advance.<o
></o
>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o
referrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <v
ath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></v
ath></v:stroke></v:shapetype><v:shape id="Picture_x0020_3" style="width: 282.6pt; height: 446.4pt;" type="#_x0000_t75" alt="" o:spid="_x0000_i1025"> <v:imagedata o:href="cid:image004.png@01D2BF41.FDE812F0" src="file:///C:\Users\vmoore\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape><o
>[TABLE="width: 283"]
<tbody>[TR]
[TD="class: xl114, width: 118, bgcolor: transparent"]Day
[/TD]
[TD="class: xl115, width: 82, bgcolor: transparent"]Batch Date
[/TD]
[TD="class: xl117, width: 85, bgcolor: #F2DCDB"]Deposit Date
[/TD]
[TD="class: xl118, width: 90, bgcolor: #F2DCDB"] Amount
[/TD]
[/TR]
[TR]
[TD="class: xl142, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl143, bgcolor: transparent"]1-Apr
[/TD]
[TD="class: xl144, bgcolor: transparent"][/TD]
[TD="class: xl145, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]2-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]3-Apr
[/TD]
[TD="class: xl125"][highlight]6-Apr
[/TD]
[TD="class: xl126"] 256.00[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]4-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 134.35
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]5-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 50.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]7-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 230.35
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]7-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 173.50
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]8-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 455.00
[/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]9-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl125"][highlight]13-Apr
[/TD]
[TD="class: xl126"] 195.45[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]11-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 180.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]12-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 229.65
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]14-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 45.62
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]14-Apr
[/TD]
[TD="class: xl125"][highlight]18-Apr
[/TD]
[TD="class: xl126"] 40.00[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]15-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"][/TD]
[TD="class: xl126, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]16-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]17-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]18-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 57.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]18-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 314.20
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]19-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 13.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 40.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 15.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]22-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"][/TD]
[TD="class: xl126, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl122, bgcolor: transparent"][/TD]
[TD="class: xl123, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl140, bgcolor: transparent, colspan: 2"] Number of Deposit Days
[/TD]
[TD="class: xl138, bgcolor: transparent"] 16
[/TD]
[TD="class: xl138, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl140, bgcolor: transparent, colspan: 2"] [highlight]Number of Occurences [/highlight][highlight]
[/TD]
[TD="class: xl138, bgcolor: transparent"] 3[/highlight]
[/TD]
[TD="class: xl138, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl121, bgcolor: transparent"]Percentage
[/TD]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl141, bgcolor: transparent, align: right"]19%
[/TD]
[TD="class: xl139, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
</o
>


<o


I need help with a formula for the number of occurrences at the bottom of the Deposit date column.<o


Based upon the entered data the number should equal the number of highlighted cells which is “3”.<o


The highlights occur through conditional formatting when the date of deposit is 2 or 3 days greater than the batch date. How can I automate this value so that it will automatically filter for the value of highlighted cells? This data will change monthly so I need the formula to read the highlighted cells only whenever they may occur which could be in any cell. Thanks in advance.<o


<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o




<tbody>[TR]
[TD="class: xl114, width: 118, bgcolor: transparent"]Day
[/TD]
[TD="class: xl115, width: 82, bgcolor: transparent"]Batch Date
[/TD]
[TD="class: xl117, width: 85, bgcolor: #F2DCDB"]Deposit Date
[/TD]
[TD="class: xl118, width: 90, bgcolor: #F2DCDB"] Amount
[/TD]
[/TR]
[TR]
[TD="class: xl142, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl143, bgcolor: transparent"]1-Apr
[/TD]
[TD="class: xl144, bgcolor: transparent"][/TD]
[TD="class: xl145, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]2-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]3-Apr
[/TD]
[TD="class: xl125"][highlight]6-Apr
[/TD]
[TD="class: xl126"] 256.00[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]4-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 134.35
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]5-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 50.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]7-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 230.35
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]7-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 173.50
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]8-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 455.00
[/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]9-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl125"][highlight]13-Apr
[/TD]
[TD="class: xl126"] 195.45[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]11-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 180.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]12-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 229.65
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]14-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 45.62
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]14-Apr
[/TD]
[TD="class: xl125"][highlight]18-Apr
[/TD]
[TD="class: xl126"] 40.00[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]15-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"][/TD]
[TD="class: xl126, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]16-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]17-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]18-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 57.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]18-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 314.20
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]19-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 13.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 40.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 15.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]22-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"][/TD]
[TD="class: xl126, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl122, bgcolor: transparent"][/TD]
[TD="class: xl123, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl140, bgcolor: transparent, colspan: 2"] Number of Deposit Days
[/TD]
[TD="class: xl138, bgcolor: transparent"] 16
[/TD]
[TD="class: xl138, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl140, bgcolor: transparent, colspan: 2"] [highlight]Number of Occurences [/highlight][highlight]
[/TD]
[TD="class: xl138, bgcolor: transparent"] 3[/highlight]
[/TD]
[TD="class: xl138, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl121, bgcolor: transparent"]Percentage
[/TD]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl141, bgcolor: transparent, align: right"]19%
[/TD]
[TD="class: xl139, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
</o
