+ Reply to Thread
Results 1 to 3 of 3

SUMIF with Stratification help

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    112

    SUMIF with Stratification help

    Hello All,
    Hope everyone is well.
    I need some help creating a formula that will count/ sum values based off a defined stratification bucket. On the Summary Tab, you will see I need to calculate in B3 the number of "indirect" (this comes from the "Data" tab - column "F") transactions that had an invoice price (from the Data tab) less than $0 and then $0-$5,000, and finally invoice price $5,001-$25,000. Next, instead of counting the transactions, I need to calculate the trade-in value (sum) for these stratification buckets as well. The Trade-in value is the Data tab, column "I".

    Is there a way to write a sumif / counting formula to solve my problem?

    Any and all help is much appreciated!

    .
    .
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: SUMIF with Stratification help

    if I got the columns correct you can try these four formulas...
    1st count records for indirect sales: =COUNTIFS(Data!F:F,"Indirect Sales",Data!J:J,"<"&0)
    2nd count records =COUNTIFS(Data!F:F,"Indirect Sales",Data!J:J,">="&0,Data!J:J,"<="&5000) - then adjust the numbers for the last range.
    1st sum =SUMIFS(Data!J:J,Data!F:F,"indirect sales",Data!J:J,"<"&0)
    2nd sum =SUMIFS(Data!J:J,Data!F:F,"indirect sales",Data!J:J,">"&0,Data!J:J,"<="&5000) - then adjust the numbers for the last range, then repeat for direct sales.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: SUMIF with Stratification help

    Thank you!

+ 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. [SOLVED] Sumif - Trying to figure out how to tell a sumif to look for multiple naming conventions
    By Typirious in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2017, 02:11 AM
  2. [SOLVED] SUMIF: don't understand that purpose of the OFFSET inside SUMIF
    By Vitalite in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-30-2017, 03:13 AM
  3. Replies: 2
    Last Post: 06-21-2017, 06:32 PM
  4. Replies: 5
    Last Post: 06-12-2017, 06:24 PM
  5. SumIf formula or Sumif with pivottable combined question
    By mcgleeuw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2015, 01:05 PM
  6. Replies: 4
    Last Post: 12-04-2014, 02:06 PM
  7. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 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