Formula help with sumifs based on multiple criteria and between date

L

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
240
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Good day friends

I need to help out with a formula on my data sheet the problem is the given tasks and its required and doing so it will take a long and stressful time
so i need a formula that and sum total for specific criteria between date rage.

From Column A i have all Vessel names listed
From Column B Activity Range
From Column D to Colum AH i have date numbers
Now
Column AL i have Vessel Name
Column AM Start Date
Column AN Return Date
From AO to AX i have list criteria Name from Column B Activities

So i need to sumifs based on multiple criteria between dates
So let say from Column AM date 2 and AN 7
then i need to sum Column D date 2 to 7 all time based on Criteria

Thank i will appreciate any one with helping and and i hope am able to explain better

regards

Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
1Vessel NamesOperational Month & Date12345678910111213141516171819202122232425262728293031Total / HrsIn DaysVessel NameeStart Date & Time (Arrival Time at MOSB)Return to MOSB Date & TimeWaiting VTSWaiting COP MOSBWorking MOSBSteamingWorking OffshoreWorking Others Marine SupplyWaiting OffshoreWaiting DaylightWaiting WeatherMaintenance
2A-CHLOEWorking MOSBADNOC Base Jetty Cargo Operation15.4155.314.51.21114.412.92.892.53.85A-CHLOE02/01/23 06:2007/01/23 01:20
3A-CHLOEWaiting COP MOSBWaitng ADNOC Berthing Instruction00.00A-CHLOE07/01/23 01:2011/01/23 18:30
4A-CHLOEWaiting COP MOSBWaiting Base Cargo Operation00.00A-CHLOE11/01/23 18:3019/01/23 01:10
5A-CHLOEWaiting ADOF SupplierWaitng Drilling Material00.00A-CHLOE19/01/23 01:1025/01/23 08:50
6A-CHLOEWaiting COP MOSBCargo Operation Suspended00.00A-CHLOE25/01/23 08:5002/02/23 07:00
7A-CHLOEWaiting VTSWaiting VTS permission1.64.3813.90.58A-GRACE01/01/23 10:2006/01/23 06:50
8A-CHLOEWaiting VTSWaiting on Channel Closure00.00A-GRACE06/01/23 06:5010/01/23 01:25
9A-CHLOEWaiting WeatherWaitinhg WOW , FOG / POOR VISIBILITY00.00A-GRACE10/01/23 01:2519/01/23 08:00
10A-CHLOESteaming In Transit / Steaming to Location 3.18.6711.43.911.8130.53.37.59.9803.33A-GRACE19/01/23 08:0025/01/23 01:30
11A-CHLOEWorking Free-PortFree-Port / Mus-Port00.00A-GRACE25/01/23 01:3031/01/23 18:25
12A-CHLOEWaiting COP Free-PortStandby location ( Waiting )00.00A-GRACE31/01/23 18:2503/02/23 23:10
Sheet1
Cell Formulas
RangeFormula
AI2:AI12AI2=SUM(D2:AH2)
AJ2:AJ12AJ2=AI2/24
 

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.
Sort by date Sort by votes
There are things I don't understand.
Lukma said:
From Column D to Colum AH i have date numbers
Click to expand...
I can see 15.4 as April 15th. But there is no year. Further to the right is 15. What kind of date number would that be without month and year?
Lukma said:
So i need to sumifs based on multiple criteria between dates
Click to expand...
Not clear what you mean by "multiple criteria".
Lukma said:
So let say from Column AM date 2 and AN 7
Click to expand...
Neither 2 nor 7 are in these columns. Because they are no dates.
Lukma said:
hen i need to sum Column D date 2 to 7 all time based on Criteria
Click to expand...
Column D has no data in it.
Lukma said:
and i hope am able to explain better
Click to expand...
Please do explain better. And show the expected outcome.
 
Upvote 0
does this work for you
=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))

i'm assuming just looking at the day() and the month or year does not matter - that will depend a lot on how you structure the data

Expected results are always good to show in a sample - so any formula provided can be tested against the results you expect


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
1Vessel NamesOperational Month & Date12345678910111213141516171819202122232425262728293031Total / HrsIn DaysVessel NameeStart Date & Time (Arrival Time at MOSB)Return to MOSB Date & TimeWaiting VTSWaiting COP MOSBWorking MOSBSteamingWorking OffshoreWorking Others Marine SupplyWaiting OffshoreWaiting DaylightWaiting WeatherMaintenance
2A-CHLOEWorking MOSBADNOC Base Jetty Cargo Operation15.4155.314.51.21114.412.92.892.53.85416667A-CHLOE1/2/23 6:201/7/23 1:201.6015.418.7000000
3A-CHLOEWaiting COP MOSBWaitng ADNOC Berthing Instruction00A-CHLOE1/7/23 1:201/11/23 18:300020.311.4000000
4A-CHLOEWaiting COP MOSBWaiting Base Cargo Operation00A-CHLOE1/11/23 18:301/19/23 1:1012.303240.6000000
5A-CHLOEWaiting ADOF SupplierWaitng Drilling Material00A-CHLOE1/19/23 1:101/25/23 8:500014.411.3000000
6A-CHLOEWaiting COP MOSBCargo Operation Suspended00A-CHLOE1/25/23 8:502/2/23 7:000000000000
7A-CHLOEWaiting VTSWaiting VTS permission1.64.3813.90.57916667A-GRACE1/1/23 10:201/6/23 6:500000000000
8A-CHLOEWaiting VTSWaiting on Channel Closure00A-GRACE1/6/23 6:501/10/23 1:250000000000
9A-CHLOEWaiting WeatherWaitinhg WOW , FOG / POOR VISIBILITY00A-GRACE1/10/23 1:251/19/23 8:000000000000
10A-CHLOESteaming In Transit / Steaming to Location 3.18.6711.43.911.8130.53.37.59.9803.33333333A-GRACE1/19/23 8:001/25/23 1:300000000000
11A-CHLOEWorking Free-PortFree-Port / Mus-Port00A-GRACE1/25/23 1:301/31/23 18:250000000000
12A-CHLOEWaiting COP Free-PortStandby location ( Waiting )00A-GRACE1/31/23 18:252/3/23 23:100000000000
13
Sheet1
Cell Formulas
RangeFormula
AI2:AI12AI2=SUM(D2:AH2)
AJ2:AJ12AJ2=AI2/24
AO2:AX12AO2=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))
 
Upvote 0
Solution
Dear etaf

many thanks (y):) it's super great, just exactly what i need to get my work faster and easy i was so worried thinking of this all night.

my concern was raised by Shift-del, which i understand that the date from column D to AH is just number, but am super happy that etaf understand it all.

now i have to increase the range to complete my report today

Am also using this to say thanks to everyone out there.

Regards



etaf said:
does this work for you
=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))

i'm assuming just looking at the day() and the month or year does not matter - that will depend a lot on how you structure the data

Expected results are always good to show in a sample - so any formula provided can be tested against the results you expect


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
1Vessel NamesOperational Month & Date12345678910111213141516171819202122232425262728293031Total / HrsIn DaysVessel NameeStart Date & Time (Arrival Time at MOSB)Return to MOSB Date & TimeWaiting VTSWaiting COP MOSBWorking MOSBSteamingWorking OffshoreWorking Others Marine SupplyWaiting OffshoreWaiting DaylightWaiting WeatherMaintenance
2A-CHLOEWorking MOSBADNOC Base Jetty Cargo Operation15.4155.314.51.21114.412.92.892.53.85416667A-CHLOE1/2/23 6:201/7/23 1:201.6015.418.7000000
3A-CHLOEWaiting COP MOSBWaitng ADNOC Berthing Instruction00A-CHLOE1/7/23 1:201/11/23 18:300020.311.4000000
4A-CHLOEWaiting COP MOSBWaiting Base Cargo Operation00A-CHLOE1/11/23 18:301/19/23 1:1012.303240.6000000
5A-CHLOEWaiting ADOF SupplierWaitng Drilling Material00A-CHLOE1/19/23 1:101/25/23 8:500014.411.3000000
6A-CHLOEWaiting COP MOSBCargo Operation Suspended00A-CHLOE1/25/23 8:502/2/23 7:000000000000
7A-CHLOEWaiting VTSWaiting VTS permission1.64.3813.90.57916667A-GRACE1/1/23 10:201/6/23 6:500000000000
8A-CHLOEWaiting VTSWaiting on Channel Closure00A-GRACE1/6/23 6:501/10/23 1:250000000000
9A-CHLOEWaiting WeatherWaitinhg WOW , FOG / POOR VISIBILITY00A-GRACE1/10/23 1:251/19/23 8:000000000000
10A-CHLOESteaming In Transit / Steaming to Location 3.18.6711.43.911.8130.53.37.59.9803.33333333A-GRACE1/19/23 8:001/25/23 1:300000000000
11A-CHLOEWorking Free-PortFree-Port / Mus-Port00A-GRACE1/25/23 1:301/31/23 18:250000000000
12A-CHLOEWaiting COP Free-PortStandby location ( Waiting )00A-GRACE1/31/23 18:252/3/23 23:100000000000
13
Sheet1
Cell Formulas
RangeFormula
AI2:AI12AI2=SUM(D2:AH2)
AJ2:AJ12AJ2=AI2/24
AO2:AX12AO2=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))
Click to expand...
 
Upvote 0
I may have NOT provided what you want - so please check the results are exactly as needed


Lukma said:
which i understand that the date from column D to AH is just number,
Click to expand...
yes dates are numbers , based on 0/1/1900 and each day increments by 1
22nd March 24 is the number 45373
23rd March 24 will be 45374
so incremented by 1


you only have 1 - 31
in column D to AH is just number,
Click to expand...
and hence i thought you wanted to sum based on what the day of the month was - 1st,2nd,3rd,4th etc

BUT , on reflection , THIS MAY NOT BE doing what you WANT
As Shift-del, has said, NO Dates in D to AH just numbers

and so it is adding up based on the day of the month

1st jan 24 to 1st feb 24 - will only look in the column 1 - it will NOT add up from column 1 to 31 - as that is a full month which is 1-jan to 1-feb
Just the days starting the 1st - regardless of the dates

Hence why i say
i'm assuming just looking at the day() and the month or year does not matter - that will depend a lot on how you structure the data

Expected results are always good to show in a sample - so any formula provided can be tested against the results you expect
Click to expand...

and
DAY(date cell) will just get the day of the month
hence
*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))
and that the data is looking at the day of the start and stop dates
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
390
archibell
A
L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
130
Peter_SSs
Peter_SSs
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
240
Lukma
L
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
239
Peter_SSs
Peter_SSs
B
  • Solved
Sumifs Hours Worked Between Two Dates and Times with Multiple Criteria
2 3
Replies
24
Views
682
Fluff
Fluff

Forum statistics

Threads
1,215,113
Messages
6,123,165
Members
449,099
Latest member
bes000

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