Thanks:  0
Likes:  0

1. 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

2. 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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•