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>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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>
 
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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