Counting basis criteria in corresponding range

hemantmohan.1

New Member
Joined
Oct 26, 2010
Messages
30
Hello Everyone,

I'm afraid i am not able to put the picture of the file for better understanding. Though, i tried and failed. I will try my best to explain. In the below grid-1, in front of the date (1-Feb), i want to insert a formula that uses the grid2 (mentioned even below). The formula should be able to count the intervals wherein the value is between 2-4 basis the dates (1-Feb) only.

I have tried using sumproduct(--(Date range="1-Feb"),ABD RATE). But it didn't work. I will be obliged in case anyone can help. Thanks in anticipation.

GRID1
DATEFORECAST
VOL
ACTUAL
VOL
CALLS ABNCALLS ANSANS in ThresholdVol
Error%
1-Feb757238284424618038348160-8.6
2-Feb738718106722747879345842-8.9
3-Feb536055888526795620632577-9.0
4-Feb40743384981638482383015.8
5-Feb810698916722848688349157-9.1
6-Feb77940794432467919773564-1.9
7-Feb78291786689167775255288-0.5
8-Feb736687533910037433662822-2.2
9-Feb70555759033937551065921-7.0
10-Feb51135562819495533239182-9.1
11-Feb396944558019014367923841-12.9
12-Feb793589064557088493724702-12.5
13-Feb780898427112228304961625-7.3
14-Feb000000.0
15-Feb000000.0
16-Feb000000.0
17-Feb000000.0
18-Feb000000.0
19-Feb000000.0
20-Feb000000.0
21-Feb000000.0
22-Feb000000.0
23-Feb000000.0
24-Feb000000.0
25-Feb000000.0
26-Feb000000.0
27-Feb000000.0
28-Feb000000.0
1-Mar000000.0
2-Mar000000.0
3-Mar000000.0
GRID2 OPERATIONSSTAFF
INTERVALSTIMEIFRABDASA
(sec)
SVLREQ+REQSCH+SCHACTUALPLANEXEC
DATERATEUNPRDUNPRD
TOTAL 2.3532.466.30119257983145741351712265
1-Feb00:000.01.499.1128.294.9167152.8158.73617
1-Feb00:300.3.0.30.999.599.773.8153140.4137.8497
1-Feb01:001.00.399.674.468133121.91255413.3
1-Feb01:301.3.00.110055.650.9104101.7108.65113.4
1-Feb02:002.00.110044.327.36177.180.9363.6
1-Feb02:302.3.0010025.3235146.367.12320.8
1-Feb03:003.0010019.217.54742.657.92515.3
1-Feb03:303.3.0010025.111.44944.545.8221.4
1-Feb04:004.00.210023.910.94742.641.921-0.7
1-Feb04:304.3.0010020.718.81934.539.3164.8
1-Feb05:005.0.816.666.924.611.13531.735.393.6
1-Feb05:305.3.06.98725.711.63329.933.873.8
1-Feb06:006.04.491.636.124.35453.54921-4.6
1-Feb06:306.3.17.384.555.1376962.660.213-2.5
1-Feb07:007.08.187.575.351.6127150.4137.182-6.5
1-Feb07:307.3.00.499.6115.686.6200182.5169.977-2.8
1-Feb08:008.0.10.399.7189.7130276280.5247.1107-21.6
1-Feb08:308.3.1.611.779.1263.3180.4352320.628280-25.1
1-Feb09:009.1.94050357.6245.1482439.3384.4113-18.7
1-Feb09:309.3.0.614.670.2443.9304.2577526.3485.7121-3.3
1-Feb10:0010.3.845.929.3515.3386.1632576.5535.910618.5
1-Feb10:3010.3.2.450.626.4568.9389.9713650.459813117.6
1-Feb11:0011.1.317.863.2605.3414.8714651.3602.99832.6
1-Feb11:3011.3.5.560.714.8639.4438.2656679.7615.49515.8
1-Feb12:0012.10.21278.1634.2475.1739674.36179515.6
1-Feb12:3012.3.7.3128.86.8644.3441.5772704.1657.611535.1
1-Feb13:0013.2.347.627.2641.7480.8795725.1682.113961.5
1-Feb13:3013.3.0.23.394.7662.9454.3728752.5697.714755.2
1-Feb14:0014.00.499.5650445.4834760.7717.716755

<colgroup><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

MartyS

Board Regular
Joined
Feb 12, 2018
Messages
197
Is this what you mean?

GRID2 on left, GRID1 on right

Formula put in cell Q4
(see below for formula)




Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWX
1OPERATIONSSTAFFDATEFORECASTACTUALCALLS ABNCALLS ANSANS in ThresholdVol
2INTERVALSTIMEIFRABDASASVLREQ+REQSCH+SCHACTUALPLANEXECVOLVOLError%
3(sec)
4DATERATEUNPRDUNPRD301-Feb757238284424618038348160-8.6
5TOTAL2.3532.466.311925798314574135171226502-Feb738718106722747879345842-8.9
601-Feb00:000.01.499.1128.294.9167152.8158.7361703-Feb536055888526795620632577-9
701-Feb00:300.3.0.30.999.599.773.8153140.4137.849704-Feb40743384981638482383015.8
801-Feb01:001.00.399.674.468133121.91255413.305-Feb810698916722848688349157-9.1
901-Feb01:301.3.00.110055.650.9104101.7108.65113.406-Feb77940794432467919773564-1.9
1001-Feb02:002.00.110044.327.36177.180.9363.607-Feb78291786689167775255288-0.5
1101-Feb02:302.3.0010025.3235146.367.12320.808-Feb736687533910037433662822-2.2
1201-Feb03:003.0010019.217.54742.657.92515.309-Feb70555759033937551065921-7
1301-Feb03:303.3.0010025.111.44944.545.8221.410-Feb51135562819495533239182-9.1
1401-Feb04:004.00.210023.910.94742.641.921-0.711-Feb396944558019014367923841-12.9
1501-Feb04:304.3.0010020.718.81934.539.3164.812-Feb793589064557088493724702-12.5
1601-Feb05:005.0.816.666.924.611.13531.735.393.613-Feb780898427112228304961625-7.3
1701-Feb05:305.3.06.98725.711.63329.933.873.814-Feb000000
1801-Feb06:006.04.491.636.124.35453.54921-4.615-Feb000000
1901-Feb06:306.3.17.384.555.1376962.660.213-2.516-Feb000000
2001-Feb07:007.08.187.575.351.6127150.4137.182-6.517-Feb000000
2101-Feb07:307.3.00.499.6115.686.6200182.5169.977-2.818-Feb000000
2201-Feb08:008.0.10.399.7189.7130276280.5247.1107-21.619-Feb000000
2301-Feb08:308.3.1.611.779.1263.3180.4352320.628280-25.120-Feb000000
2401-Feb09:009.1.94050357.6245.1482439.3384.4113-18.721-Feb000000
2501-Feb09:309.3.0.614.670.2443.9304.2577526.3485.7121-3.322-Feb000000
2601-Feb10:0010.3.845.929.3515.3386.1632576.5535.910618.523-Feb000000
2701-Feb10:3010.3.2.450.626.4568.9389.9713650.459813117.624-Feb000000
2801-Feb11:0011.1.317.863.2605.3414.8714651.3602.99832.625-Feb000000
2901-Feb11:3011.3.5.560.714.8639.4438.2656679.7615.49515.826-Feb000000
3001-Feb12:0012.10.21278.1634.2475.1739674.36179515.627-Feb000000
3101-Feb12:3012.3.7.3128.86.8644.3441.5772704.1657.611535.128-Feb000000
3201-Feb13:0013.2.347.627.2641.7480.8795725.1682.113961.501-Mar000000
3301-Feb13:3013.3.0.23.394.7662.9454.3728752.5697.714755.202-Mar000000
3401-Feb14:0014.00.499.5650445.4834760.7717.71675503-Mar000000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet15

Worksheet Formulas
CellFormula
Q4=COUNTIFS($A6:$A34,$R4,$E6:$E34,">=2",$E6:$E34,"<=4")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

hemantmohan.1

New Member
Joined
Oct 26, 2010
Messages
30
Thanks MartyS. This one works, Though, i had forgot earlier and i was able to recollect a formula i had used in the past. Here you go.... =(SUMPRODUCT(--($A$204:$A$1691=B169),--($E$204:$E$1691>=2),--($E$204:$E$1691<=4))
However, Can i ask for some further help. Is there a way...basis the range in Col A... can i multiply corresponding numbers in two different columns ?
 

MartyS

Board Regular
Joined
Feb 12, 2018
Messages
197
However, Can i ask for some further help. Is there a way...basis the range in Col A... can i multiply corresponding numbers in two different columns ?

I'm not sure if you need to raise this as a new post (forum rules)?

Is this what you mean?


Excel 2010
ABCDEF
1CriteriaMult1Mult2DateResult
201/02/201810101/02/2018360
301/02/201810202/02/20181820
401/02/201810303/02/20181080
501/02/2018104
601/02/2018105
701/02/2018106
801/02/2018107
901/02/2018108
1002/02/2018201
1102/02/2018202
1202/02/2018203
1302/02/2018204
1402/02/2018205
1502/02/2018206
1602/02/2018207
1702/02/2018208
1802/02/2018209
1902/02/20182010
2002/02/20182011
2102/02/20182012
2202/02/20182013
2303/02/2018301
2403/02/2018302
2503/02/2018303
2603/02/2018304
2703/02/2018305
2803/02/2018306
2903/02/2018307
3003/02/2018308

<tbody>
</tbody>




Worksheet Formulas
CellFormula
F2=SUMPRODUCT(--($A$2:$A$30=$E2),$B$2:$B$30,$C$2:$C$30)
F3=SUMPRODUCT(--($A$2:$A$30=$E3),$B$2:$B$30,$C$2:$C$30)
F4=SUMPRODUCT(--($A$2:$A$30=$E4),$B$2:$B$30,$C$2:$C$30)

<tbody>
</tbody>

<tbody>
</tbody>




Here, I've multiplied contents of [column B] by [column C] using the DATE in [column A] as the variable to group on, when matching the date in [column E].
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,260
Members
410,905
Latest member
Extjel
Top