Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Sumif formula/countif

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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