+ Reply to Thread
Results 1 to 11 of 11

Formula to Count Base On Criteria

  1. #1
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Formula to Count Base On Criteria

    Hello All,

    Is there a formula which will look into the Data Dump Tab and match from the Table Tab the Sold Mgr Name and Product listed under them in between the dates on top from the Table tab to the Data dump Table and return the account count from column A in the Data dump Tab. But it wonít count the account multiple times.

    Example if John sold a Notebook to Walmart, RiteAide, and Homedepot between 2016-01 to 2016-12 it should count Walmart, RiteAide, and Homedepo as 3 regardless if he sold a Notebook on 2016-02 and again 2016-06 and again 2016-09 and again 2016-12 for each account.
    In the attachment Iíll added what the answer should be for John if formula created works. The formula should work for the other Sold Mgr Names as well with just adjustment picking one of the other Sold Mgr Names.
    Iíve also put another tab with a different view of the Chart but it would be the same as the Table tab result if it would be easier to understand that view for you.

    I know the formula has to deal with Sum if of countif and with frequency.

    See attachment.

    I would greatly appreciate the help.

    Thank you,
    Attached Files Attached Files
    Last edited by lsantana; 07-13-2017 at 12:20 PM. Reason: Updated information for better understanding of requirement

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

    Re: Formula to Count Base On Criteria

    Try

    in C8

    =IF(COUNTIFS('Data Dump'!$B:$B,$B$7,'Data Dump'!$C:$C,">=" & $B$3,'Data Dump'!$C:$C,"<=" & $C$3,INDEX('Data Dump'!$D:$H,,MATCH($B8,'Data Dump'!$D$1:$H$1,0)),1),1,0)
    Copy down for John

    Change highlighted range to next mgr.

    I suggest a layout with products in column B and Account managers in columns C to Z so you create a ore "formula-friendly" layout

    See Sheet1 of attached
    Attached Files Attached Files
    Last edited by JohnTopley; 07-13-2017 at 02:41 AM.

  3. #3
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Formula to Count Base On Criteria

    Quote Originally Posted by JohnTopley View Post
    Try

    in C8

    =IF(COUNTIFS('Data Dump'!$B:$B,$B$7,'Data Dump'!$C:$C,">=" & $B$3,'Data Dump'!$C:$C,"<=" & $C$3,INDEX('Data Dump'!$D:$H,,MATCH($B8,'Data Dump'!$D$1:$H$1,0)),1),1,0)
    Copy down for John

    Change highlighted range to next mgr.

    I suggest a layout with products in column B and Account managers in columns C to Z so you create a ore "formula-friendly" layout

    See Sheet1 of attached
    Hello John,

    Formula works fine if there's one account for the Sold Manager. But with if the Sold manager has multiple account as seen on the new attachment. It would count the other accounts as one within the date range.

    Please see new attachment.

    thank you for your help is greatly appreciated.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

    Re: Formula to Count Base On Criteria

    So you want a count by each account for each Account Mgr?

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,921

    Re: Formula to Count Base On Criteria

    Better to Arrange Items Horizontally and Sold Managers Name Vertically to avoid unnecessary confusion
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Formula to Count Base On Criteria

    Quote Originally Posted by JohnTopley View Post
    So you want a count by each account for each Account Mgr?
    Yes it should count each account once by Sold Mgr Name and by product. I hide the sheet1 from your original reply with your formula if you would like I've re-attached with your view. but for example:

    for Sold Manager John for Product Notebook within the data range should count 3 since it would count Walmart, RiteAide, and Homedepot once.

    Thank you,
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Formula to Count Base On Criteria

    Quote Originally Posted by nflsales View Post
    Better to Arrange Items Horizontally and Sold Managers Name Vertically to avoid unnecessary confusion
    Hello Nflsales,

    I've attached the data with the view requested. As i mentioned to JohnTopley it should count each account once by Sold Mgr Name and by product for example:

    for Sold Manager John for Product Notebook within the data range should count 3 since it would count Walmart, RiteAide, and Homedepot once.

    Thank you nflsales I greatly appreciate your help as well.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Formula to Count Base On Criteria

    This proposed solution isn't as elegant as I would like, however perhaps it will help until something better comes along.
    A helper column is added to the data dump, populated by the formula: =DATE(LEFT(C2,4),RIGHT(C2,2),1)
    A Pivot Table is produced as shown on sheet 3.
    The second table on the 'Different Data View' sheet is basically populated by the formula: =SUMIFS(Sheet3!C$4:C$11,Sheet3!$A$4:$A$11,$B15)
    Instead of dictating the range of dates, B2 and C2 display the range selected in the Pivot Table's Report Filter by referencing the values in H12:I12 of the Pivot Table.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Formula to Count Base On Criteria

    Quote Originally Posted by JeteMc View Post
    This proposed solution isn't as elegant as I would like, however perhaps it will help until something better comes along.
    A helper column is added to the data dump, populated by the formula: =DATE(LEFT(C2,4),RIGHT(C2,2),1)
    A Pivot Table is produced as shown on sheet 3.
    The second table on the 'Different Data View' sheet is basically populated by the formula: =SUMIFS(Sheet3!C$4:C$11,Sheet3!$A$4:$A$11,$B15)
    Instead of dictating the range of dates, B2 and C2 display the range selected in the Pivot Table's Report Filter by referencing the values in H12:I12 of the Pivot Table.
    Let us know if you have any questions.
    Hello JeteMC,

    I greatly appreciate the help I would just need a more dynamic formula since some people who will be viewing this file are verrry novice with excel but it do temporary work for myself so very thankful.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

    Re: Formula to Count Base On Criteria

    See attached:

    Sheet1 has list of Accounts: Named Range called "Accounts"

    in "Different View Data"

    in in C21

    =SUM(IF(COUNTIFS('Data Dump'!$B:$B,$B21,'Data Dump'!$C:$C,">="&Date1,'Data Dump'!$C:$C,"<="&Date2,INDEX('Data Dump'!$D:$H,,MATCH(C$20,'Data Dump'!$D$1:$H$1,0)),1,'Data Dump'!$A:$A,Accounts),1,0))


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy across and down

    the dates in "Table" B3 and C3 are named ranges called "Date1" and "Date2"

  11. #11
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Formula to Count Base On Criteria

    Quote Originally Posted by JohnTopley View Post
    See attached:

    Sheet1 has list of Accounts: Named Range called "Accounts"

    in "Different View Data"

    in in C21

    =SUM(IF(COUNTIFS('Data Dump'!$B:$B,$B21,'Data Dump'!$C:$C,">="&Date1,'Data Dump'!$C:$C,"<="&Date2,INDEX('Data Dump'!$D:$H,,MATCH(C$20,'Data Dump'!$D$1:$H$1,0)),1,'Data Dump'!$A:$A,Accounts),1,0))


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy across and down

    the dates in "Table" B3 and C3 are named ranges called "Date1" and "Date2"
    oh my god yes thank you so much this worked beautifully.

+ 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. Macro or Formula to search worksheets and return results base on criteria
    By Shellybelly in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2016, 01:18 PM
  2. [SOLVED] count and sum base on reference name
    By gondal in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-09-2016, 09:08 AM
  3. VBA : insert blank rows between base upon count of each rows with criteria..need help??
    By Jhon Mustofa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-10-2013, 12:30 AM
  4. count base on the day of the week
    By sanlen in forum Excel General
    Replies: 9
    Last Post: 01-27-2011, 08:40 AM
  5. Calculate base on criteria
    By VBisgreat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-07-2010, 01:07 PM
  6. count the number of occurance base on criteria
    By stoey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2009, 01:31 PM
  7. I would like to count the # of times a value occurs in Col B base
    By shopaholic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2005, 12:30 PM

Tags for this Thread

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