Sumif formula/countif

lars

Board Regular
Joined
Mar 27, 2002
Messages
105
My data is laid out as follows with ship to the left repeating for the number of voyages
and amount in the other columns, I want to find the ship and sum column N based on dates

Column A Column G Column N
Grand 1/05/2002 2827
Grand 5/08/2002 3000
Grand 7/25/2002 2897
Sea 2/06/2002 4650
Sea 5/28/2002 5000
Sea 7/30/2002 4500
Pacific 4/10/2002 3200
Pacific 5/28/2002 2500

The months are from the beginning of the year to the end of 2002 and I want only May- August to apply to the sumif. So if you see Grand and then see anything from May-August sum it - do that for all the ships and also a countif as well to count the number of times the voyage was listed for the 4 months May-Aug

So for grand the sumif should total 5897 and the countif to be 2

Can this be done? hope this makes sense

Thanks
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
On 2002-04-09 11:31, lars wrote:
My data is laid out as follows with ship to the left repeating for the number of voyages
and amount in the other columns, I want to find the ship and sum column N based on dates

Column A Column G Column N
Grand 1/05/2002 2827
Grand 5/08/2002 3000
Grand 7/25/2002 2897
Sea 2/06/2002 4650
Sea 5/28/2002 5000
Sea 7/30/2002 4500
Pacific 4/10/2002 3200
Pacific 5/28/2002 2500

The months are from the beginning of the year to the end of 2002 and I want only May- August to apply to the sumif. So if you see Grand and then see anything from May-August sum it - do that for all the ships and also a countif as well to count the number of times the voyage was listed for the 4 months May-Aug

So for grand the sumif should total 5897 and the countif to be 2

Can this be done? hope this makes sense

Thanks

Hi,

For the count:
=SUMPRODUCT((A1:A8="Grand")*(MONTH(G1:G8)>=5)*(MONTH(G1:G8)<=8))

For the total:
=SUMPRODUCT((A1:A8="Grand")*(MONTH(G1:G8)>=5)*(MONTH(G1:G8)<=8)*(N1:N8))

Adjust the ranges to suit. All constants and text can be references to cells as well.

HTH,
Jay
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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