+ Reply to Thread
Results 1 to 9 of 9

Calculating Sum in one column, based on criteria in another column

  1. #1
    Registered User
    Join Date
    03-30-2014
    Location
    East Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Unhappy Calculating Sum in one column, based on criteria in another column

    I have 2 columns; 1 with "dates" of incidents, the other with "number of incidents". The dates are not in any order but I want to be able to count "number of incidents" for certain time periods. e.g. If dates are between 01/02/14 and 28/02/14, what are the number in incidents. This number would be represented on a new worksheet.

    I've looked at COUNTIF/S, SUMIF but just cant get there. Can anyone help please?

  2. #2
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Calculating Sum in one column, based on criteria in another column

    Countifs will do. Try this:
    =COUNTIFS(A2:A100,">=01-02-2014",A2:A100,"<=28-02-2014")

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Calculating Sum in one column, based on criteria in another column

    Please find the attached sheet to have an idea about how to achieve that. Make changes as per your sheet layout.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Calculating Sum in one column, based on criteria in another column

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Where dates in column A, incidents in column B.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  5. #5
    Registered User
    Join Date
    03-30-2014
    Location
    East Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Calculating Sum in one column, based on criteria in another column

    Quote Originally Posted by RudiS View Post
    Countifs will do. Try this:
    =COUNTIFS(A2:A100,">=01-02-2014",A2:A100,"<=28-02-2014")
    Hi
    Thank you for your suggestion. Unfortunately it does work as it's not referring to the data in column B.

    Another answer has worked though.

    Many thanks, have a nice evening.

  6. #6
    Registered User
    Join Date
    03-30-2014
    Location
    East Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Calculating Sum in one column, based on criteria in another column

    Hi Sktneer

    This is great thanks; I can adapt my spreadsheet to make this work for me.

    Really grateful. Have a nice evening (or whatever time of day it is there).

    Regards

  7. #7
    Registered User
    Join Date
    03-30-2014
    Location
    East Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Calculating Sum in one column, based on criteria in another column

    Hi Olly

    Many thanks for your help. All sorted now (will sleep well tonight).

    Kind regards

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Sum in one column, based on criteria in another column

    Here's another one...

    Data Range
    A
    B
    C
    D
    E
    1
    ------
    ------
    ------
    ------
    ------
    2
    3/31/2014
    85
    Feb 2014
    530
    3
    1/29/2014
    37
    4
    2/16/2014
    100
    5
    1/14/2014
    31
    6
    1/16/2014
    46
    7
    1/25/2014
    74
    8
    3/24/2014
    82
    9
    3/20/2014
    95
    10
    2/11/2014
    34
    11
    1/4/2014
    88
    12
    3/10/2014
    77
    13
    2/4/2014
    51
    14
    2/25/2014
    94
    15
    2/10/2014
    42
    16
    2/7/2014
    81
    17
    3/9/2014
    49
    18
    2/25/2014
    77
    19
    2/8/2014
    51
    20
    3/4/2014
    31


    This formula entered in E2:

    =SUMPRODUCT(--(TEXT(A2:A20,"mmm yyyy")=D2),B2:B20)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Calculating Sum in one column, based on criteria in another column

    Glad to help you. Thanks for the feedback.
    If that takes care of your question, please mark your thread as solved.
    Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This another way to say thanks to them.

+ 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] Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.
    By RobertOHare in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2013, 02:57 PM
  2. calculating column 1 based on colum on column 4
    By leongandrew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2013, 10:09 AM
  3. [SOLVED] Copy data from column to other sheets, based upon vlookup/criteria on column a
    By jedemeyer1 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-27-2013, 04:01 AM
  4. Replies: 3
    Last Post: 03-18-2011, 11:28 AM
  5. Calculating totals in a column based on a lookup in another column
    By Michael Wright via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2005, 05:06 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