Please someone help me to solve this problem. Need a formula to summarized value of specific aged amount

FMHasan

New Member
Joined
Nov 2, 2021
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
New Microsoft Excel Worksheet (2).xlsx
DEFGHIJKLMNO
20-3031-6061-9091-120120-365366- Avobe
3Bill NoBill Date AgedTkSum of TkSum of TkSum of TkSum of TkSum of TkSum of Tk
410030-06-229050000
510115-07-227535000
610230-07-226056333
710327-06-229346666
810404-10-22-650004
910505-07-228550005
1010606-06-2211489745
1110726-08-223348000
1210808-06-2211269315
1310925-05-2212650009
1411006-03-2220621365
1511131-08-222825464
1611212-09-221689750
1711301-01-2163560000
1811401-06-2148456000
1911501-10-2136245680
Pran
Cell Formulas
RangeFormula
F4:F19F4=TODAY()-E4-60
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Merhaba Hasan,

The formula you are looking for is::
J3 =SUMIFS($G$4:$G$19,$F$4:$F$19,">=0",$F$4:$F$19,"<=30")
And same for K, L, M and N.
O3 =SUMIF($G$4:$G$19,$F$4:$F$19,">=366")
 
Upvote 0
Solution
Merhaba Hasan,

The formula you are looking for is::
J3 =SUMIFS($G$4:$G$19,$F$4:$F$19,">=0",$F$4:$F$19,"<=30")
And same for K, L, M and N.
O3 =SUMIF($G$4:$G$19,$F$4:$F$19,">=366")
Thank you
 
Upvote 0
Another suggestion if you want to make the same formula all the way across.
In my sample, rows 1 & 2 could be hidden if you want.
BTW, I think that you need to change the range in cell N2. Shouldn't that range start from 121 as I have it?

22 11 27.xlsm
FGHIJKLMNO
10316191121366
23060901203659.00E+307
3AgedTk0-3031-6061-9091-120121-365366- Avobe
49050000115214104333135005205726117054116000
57535000
66056333
79346666
8-650004
98550005
1011489745
113348000
1211269315
1312650009
1420621365
152825464
161689750
1763560000
1848456000
1936245680
Sums
Cell Formulas
RangeFormula
J4:O4J4=SUMIFS($G4:$G19,$F4:$F19,">="&J1,$F4:$F19,"<="&J2)
 
Upvote 0
Another suggestion if you want to make the same formula all the way across.
In my sample, rows 1 & 2 could be hidden if you want.
BTW, I think that you need to change the range in cell N2. Shouldn't that range start from 121 as I have it?

22 11 27.xlsm
FGHIJKLMNO
10316191121366
23060901203659.00E+307
3AgedTk0-3031-6061-9091-120121-365366- Avobe
49050000115214104333135005205726117054116000
57535000
66056333
79346666
8-650004
98550005
1011489745
113348000
1211269315
1312650009
1420621365
152825464
161689750
1763560000
1848456000
1936245680
Sums
Cell Formulas
RangeFormula
J4:O4J4=SUMIFS($G4:$G19,$F4:$F19,">="&J1,$F4:$F19,"<="&J2)
Yes, That should be 121. Thank you.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

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