+ Reply to Thread
Results 1 to 10 of 10

Summation of count but with conditions

  1. 01-28-2018, 11:11 AM #1
    ell_
    ell_ is offline
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Question Summation of count but with conditions

    Hi, all

    I have 2 primary sheets that need to be applied with formulas, monthly summary and summary. Is it possible to have formulas in which:

    1. For monthly summary, the formula will sum up the total count located in row 55 and 84 (will be constant, only columns will be different since we have different months, arranged horizontally) based on name of shop (if only possible since I will have multiple sheets of shops, if this can't be done, ignore this), the month chosen from dropdown list in cell B2 of monthly summary sheet and the parameters (sale and performance percentage) respectively?

    2. For summary, the conditions are similar but the month will not be from dropdown list. Months will be listed instead.

    Hereby I attached the example of Excel file for your reference.

    Can anyone give me a hand in this? Thanks in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-28-2018, 01:47 PM #2
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Summation of count but with conditions

    Hi

    I suppose you want something like this.

    Use in 'Monthly Summary' sheet two cells D2 (first column of month data) and D3 (las column of month data) as auxiliar cells
    In D2 use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    In D3 use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    In B6 use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    In B6 use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Use similar formulas for sheet 'Summary'
    See the file for clarification
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 01-28-2018, 08:36 PM #3
    ell_
    ell_ is offline
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Summation of count but with conditions

    Actually I only want the sum of the count in the rows mentioned above. As in the count of the total 10 days but I believe you're using average? I have tried to change the formula of average to sum, works well with February but not for January. I think it's due to the formula in "Final Column" as it totals up both months instead of 1 month each.

    Not sure how to edit that.
    Last edited by ell_; 01-28-2018 at 08:55 PM.
    Reply With QuoteRegister To Reply
  4. 01-28-2018, 11:05 PM #4
    bebo021999
    bebo021999 is online now
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Summation of count but with conditions

    Actually you can use below formulas for other sheets like Shop2, shop3,...with INDIRECT function
    All formulas are array formula, confirmed by Ctrl-shift-enter
    Monthly summary sheet, cell B6:

    Please Login or Register  to view this content.
    Copy to next column, adjust B55:W55 by B84:W84
    Drag down for next shop title in column A

    Sheet Summary, cell C2:

    Please Login or Register  to view this content.
    Drag to next column and adjust 55 to 84

    (FEBRUARY has space at the end so I have to try TRIM() to eliminate it)

    Drag down
    Attached Files Attached Files
    Quang PT
    Reply With QuoteRegister To Reply
  5. 01-29-2018, 02:12 AM #5
    ell_
    ell_ is offline
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Summation of count but with conditions

    Quote Originally Posted by bebo021999 View Post 
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Can I know what's zzz and mmmm?
    Reply With QuoteRegister To Reply
  6. 01-29-2018, 10:08 PM #6
    bebo021999
    bebo021999 is online now
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Summation of count but with conditions

    Quote Originally Posted by ell_ View Post
    Can I know what's zzz and mmmm?
    1) Asumming A1 is real date, like 30/01/2018.
    TEXT(A1,"mmmm") returns string "January".
    If "mmm", it returns "Jan"

    2) LOOKUP("ZZZ",$A$2:$A3)

    Assuming A2 is text "SHOP1", A3 is blank
    It returns last text in range A2:A3 that found, it is "SHOP1" in cell A2.

    Hope it is clear for you.
    Reply With QuoteRegister To Reply
  7. 01-31-2018, 10:23 AM #7
    ell_
    ell_ is offline
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Summation of count but with conditions

    Hi, Bebo

    Will the formula detect the month and calculate sum of cells based on how many days in the month? Because I noticed that the summation is different for February and some other months.
    Reply With QuoteRegister To Reply
  8. 01-31-2018, 10:38 AM #8
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Summation of count but with conditions

    Hi

    See if is this what you want

    Use in 'Monthly Summary'!B6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Use in 'Monthly Summary'!C6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    See the file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 01-31-2018, 07:17 PM #9
    ell_
    ell_ is offline
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Summation of count but with conditions

    Hi, Jose

    The "final column" is not changing. May I know why?
    Reply With QuoteRegister To Reply
  10. 02-01-2018, 04:23 AM #10
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Summation of count but with conditions

    Yes

    The number of days in each month in this example is the same.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Summation on multiple conditions
    By chasoe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2016, 07:57 AM
  2. Data summation with multiple conditions
    By mdive in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-02-2015, 08:35 AM
  3. Excel Summation Query from IF conditions
    By jasonandmary in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2014, 08:24 PM
  4. Summation with 2 different conditions - SUMPRODUCT Can't work in this example
    By Lewis Koh in forum Excel General
    Replies: 7
    Last Post: 04-18-2010, 07:43 PM
  5. How to do summation with 2 conditions?
    By Lewis Koh in forum Excel General
    Replies: 3
    Last Post: 04-18-2010, 11:17 AM
  6. How to do summation with 2 conditions?
    By Lewis Koh in forum Excel General
    Replies: 2
    Last Post: 04-18-2010, 11:16 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1