+ Reply to Thread
Results 1 to 7 of 7

Countifs and Sumifs for multiple criteria

  1. #1
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Countifs and Sumifs for multiple criteria

    I am trying count the total number of people who live within a certain mileage of a store, and who spent a certain amount of money in the month of January based on preset mileage ranges and spend amounts. I am also trying to count the number of trips these group of individuals made in each segment. In addition, I have a column in the far right that is to calculate the average spend of these individuals for each segment.

    Attached is my test worksheet. You can see the fields I am looking to fill in with a formula on the top (in yellow), with the data set below starting on row 26. Note the data set includes data for January, February and March. Once I see the formula for January I will be able to use this for February, March and the promotion month.

    Any help would be wonderful.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Countifs and Sumifs for multiple criteria

    If I understand correctly try these.

    In cell E8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then copy that cell and paste into cells G8, I8, K8 and M8.

    Then in cell F8 this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then copy that cell and paste into cells H8, J8, L8 and N8.

    For the average in cell O8 this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does this do what you want?
    Dave

  3. #3
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Countifs and Sumifs for multiple criteria

    Thanks Dave. Actually, there are important missing criteria pieces to the formulas. More specifically, the results need to also fall within the People Spend ranges for each row as shown in B8:C18. In other words, I need to fill the rest of the yellow highlighted fields with the formulas so each spend range is calculated.

    To help clarify, below are some examples.

    # of People Columns:
    As an example, in cell E16, I need the result to be the total number of people who live within 0-10 miles of the store and spend between $75 and $100.

    # of Trips Columns:
    While the criteria used for the miles range were correct in # of People columns, the same is not true for the Trips column. The Trips just need to be the sum total of trips for people who live within the designated distance of the store and who spend an amount of money that falls within the "Peoples Spend" range. As an example, in cell F16, I need the result to be the total number of trips from people who live within 0-10 miles of the store and spend between $75 and $100.

    Average Spend Column:
    The same is true for the Average spend column on the far right. This result also need to include the Spend Range criterial in the formula. For example, in Cell O16 I need the result to be the average spend amount from people who spend between $75 and $100.

    I hope that makes sense.
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Countifs and Sumifs for multiple criteria

    I added a suitably large upper boundary of 100,000 to cell C8 in the "less than" field. Without that these formulas won't work in the first row.

    Try this in E8.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy and paste to G8, I8, K8 and M8.

    Then this in F8.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy and paste to H8, J8, L8 and N8.

    In O8 this formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Fill all formulas down.

  5. #5
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Countifs and Sumifs for multiple criteria

    This is much closer. There still seems to be an issue with the Trips formula. I believe this needs to be a sumif formula as I am trying to total up the number of trips by each of the corresponding people. I changed the data set a bit to include a few people with spend amounts in the $200 and up segments so you can see the issue at hand. For example, if you look at cell F26 you will see 3 trips by this person in the month of January and their average spend was $1001. As such, cell F8 should show 3 instead of 1. Hopefully that makes sense.

    Also, I adjusted the formula for cells E8 through Q8 by removing the upper limit of cel C8 so it was not necessary to add a large upper number in this cell.
    I also extended the range to beyond 100 miles to capture the remaining people who were not previously accounted for.
    I also added a total column at the far right to add up the # of People and #of trips so I could use this as a cross reference to confirm the calculations. As you can see, the number of people is 7,000, which is correct, but the number of trips should be 13,978 instead of 7,000. Hopefully you can help fix this one last formula.

    See attached updated version with your formulas and my fixes.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Countifs and Sumifs for multiple criteria

    I just figured out the remaining sumifs formula so this problem is now solved. I used the following
    Cell F8 =SUMIFS($F$26:$F$100000,$E$26:$E$100000,">="&E$5,$E$26:$E$100000,"<="&F$5,$G$26:$G$100000,">="&$B8)
    Cell F9 = =SUMIFS($F$26:$F$100000,$E$26:$E$100000,">="&E$5,$E$26:$E$100000,"<="&F$5,$G$26:$G$100000,">="&$B9,$G$26:$G$100000,"<"&$C9)

    Thanks again!

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Countifs and Sumifs for multiple criteria

    Super! You're welcome. Glad you got it to work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  2. [SOLVED] countifs statement with multiple criteria for multiple criteria ranges
    By mcdermott2 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-07-2015, 11:48 AM
  3. [SOLVED] SUMIFS/COUNTIFS array function question (multiple criteria)
    By akamenov88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2015, 07:08 PM
  4. [SOLVED] Countifs with multiple criteria and one criteria has a list of names
    By Beefy1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2014, 05:25 PM
  5. Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?
    By Carcophan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-08-2013, 01:43 PM
  6. Sumifs/Countifs with multiple and's and or's
    By falcontrainer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2013, 01:26 PM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

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