Poll: What do you think

+ Reply to Thread
Results 1 to 6 of 6

6 month rebooking count- Count if with either criteria met

  1. #1
    Registered User
    Join Date
    04-07-2015
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Arrow 6 month rebooking count- Count if with either criteria met

    Hi there,
    I need a formula to help me with the following:
    the sales figures for 14 month are in a pivot table. I need to calculate the number of people that have bought the product again within 6 months. I have tried count ifs but that is doubling in the instance were you have a value in the second third and forth row, etc.
    I also have tried sum product with the same problem. I need to tell excel: If the value in column B is greater than 1, and in either of the columns from C to H there is a value, on the same row, count them in order to established out if the clients that brought the product in Month 2, how many brought again over the following 6 months.
    Many thanksTest.xlsx

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 6 month rebooking count- Count if with either criteria met

    Probably not the most elegant way, but it works..

    =SUMPRODUCT((B5:B19>0)*(C5:C19+D5:D19+E5:E19+F5:F19+G5:G19+H5:H19>0))

  3. #3
    Registered User
    Join Date
    04-07-2015
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: 6 month rebooking count- Count if with either criteria met

    Hey,
    Thank you . It worked!!!
    What the * does in this formula? Never really used sumproduct and would like to understand it properly.

    Many thanks.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 6 month rebooking count- Count if with either criteria met

    You're welcome.

    Sumproduct evaluates row by row
    there are 2 expressions (questoins) with True or False answers.
    B5:B19>0 (Is the value in B >0?)
    and
    C5:C19+D5:D19+E5:E19+F5:F19+G5:G19+H5:H19>0 (is c+d+e+f+g+h > 0 ?)
    For each row, those questions evaluate to either True or False.
    When a math operation (like * + - /) is performed on a True or False Value, True is converted to 1, False is converted to 0
    So you get an array of 1's and 0's
    It will only be 1 if both questions are true
    FALSE*FALSE = 0*0 = 0
    TRUE*FALSE = 1*0 = 0
    FALSE*TRUE = 0*1 = 0
    TRUE*TRUE = 1*1 = 1

    It then sums all the 1's and 0's to give a count of how many rows exist with both questions resulting in true.
    Last edited by Jonmo1; 04-08-2015 at 12:52 PM.

  5. #5
    Registered User
    Join Date
    04-07-2015
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: 6 month rebooking count- Count if with either criteria met

    Amazing,
    Thank you.

  6. #6
    Registered User
    Join Date
    04-07-2015
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: 6 month rebooking count- Count if with either criteria met

    Hey,
    I hope i can rely on your help again.
    I need to do the sum now if either of the conditions are met.
    Eg., i need the sum of revenue in a month of the clients that brought again in either of the next 6 months.

    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] Count with criteria and month
    By werko in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2015, 08:00 PM
  2. Count occurrances in month and year given certain criteria?
    By Tarheel8181 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2014, 10:03 PM
  3. Replies: 0
    Last Post: 02-14-2012, 04:09 PM
  4. Date Count By Month with Multiple Criteria
    By usc1382 in forum Excel General
    Replies: 7
    Last Post: 09-27-2011, 09:39 AM
  5. Count with multiple criteria - month and name
    By tandridge in forum Excel General
    Replies: 3
    Last Post: 04-05-2011, 06:55 AM

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