+ Reply to Thread
Results 1 to 2 of 2

SUMIF with Stratification help

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    62

    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 Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,515

    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

+ 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