Sumif formula/countif

[lars]

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

[Jay Petrulis]

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

[lars]

thanks!!!!! I'll give it a shot