+ Reply to Thread
Results 1 to 3 of 3

Formula needed for countif or countifs (may also need a sumif)

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Formula needed for countif or countifs (may also need a sumif)

    Hi All

    I was wondering if someone could help me with this formula. Basically at the moment I have this formula:
    =COUNTIFS('Buyers List 2013'!R:R, "Africa", 'Buyers List 2013'!$L:$L, ">=1/1/2013", 'Buyers List 2013'!$L:$L, "<1/2/2013")

    If date in column L is equal or greater than 1/1/2013 and less than 1/2/2013 count corresponding cells in column R that contain Africa.

    I would like to keep this formula but instead of counting cells containing Africa, sum the volume in Column U for that row. Does this make sense and is it possible please?

    =COUNTIFS('Buyers List 2013'!R:R, "Africa", 'Buyers List 2013'!$L:$L, ">=1/1/2013", 'Buyers List 2013'!$L:$L, "<1/2/2013")

    To further explain I would like to sheet1 column a2 to equal the sum of all the volumes in cells in column U that correspond with Africa in cells in column R in between the dates of 1/1/2013 and 1/2/2013.

    I hope this makes sense.

    Cheers

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula needed for countif or countifs (may also need a sumif)

    hi Petefav. to make the dates more universal with all kinds of users, you should use:
    =COUNTIFS('Buyers List 2013'!R:R, "Africa", 'Buyers List 2013'!$L:$L, ">=1jan2013", 'Buyers List 2013'!$L:$L, "<1feb2013")

    that is assuming you wanted 1 jan to 31 jan, because i'm assuming dmy. if someone's region settings is in mdy, it will read from 1 Jan to 2 Jan 2013 using your formulas.

    and your SUMIFS would be:
    =SUMIFS('Buyers List 2013'!U:U,'Buyers List 2013'!R:R, "Africa", 'Buyers List 2013'!$L:$L, ">=1jan2013", 'Buyers List 2013'!$L:$L, "<1feb2013")

    just those changes in red

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Formula needed for countif or countifs (may also need a sumif)

    Try sumifs....

    =sumifs(Sum_Range,'Buyers List 2013'!R:R,"Africa",'Buyers List 2013'!$L:$L,">="&date(2013,1,1), 'Buyers List 2013'!$L:$L,"<"&date(2013,2,1))


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1