+ Reply to Thread
Results 1 to 4 of 4

getting the sum based on column criteria

  1. #1
    Registered User
    Join Date
    11-03-2020
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    21

    getting the sum based on column criteria

    Hi,

    Can anyone here help me with the formula to get the total summation based on the column criteria by date. I have attached a sample file.

    On Raw file, there is a data where in I need to get the total Summation on each column based on the output tab.

    For example, on column US400 for May 1, 2022 the total sum is 4601.

    Hope can anyone has an idea? Thanks much!
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,617

    Re: getting the sum based on column criteria

    Your dates in column A of the Raw sheet are in text format, so you need to convert them first. Select column A then click on Data | Text-to-columns | Next | Next then choose Date with MDY then click Finish.

    Then you can use this formula in C2 of the Output sheet:

    =SUMIFS(Raw!B:B,Raw!$A:$A,$A2)

    Copy across to E2, then copy down as required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-03-2020
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    21

    Re: getting the sum based on column criteria

    thanks Pete_UK.

    Also, can we make it dynamic? as some of the columns on raw data are moving. for example code, US400 is in column F and PTY is in column Y.

    Thanks again

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,617

    Re: getting the sum based on column criteria

    You can use this variation in C2:

    =IFERROR(SUMIFS(INDEX(Raw!$B:$D,0,MATCH(TRIM(C$1),Raw!$B$1:$D$1,0)),Raw!$A:$A,$A2),"")

    to match on the column headings. Change the ranges in red to suit your actual file, then copy across and down as required.

    Hope this helps.

    Pete

+ 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] Unique List From Row Name Based On Criteria from Column Data Header and Column Contents
    By jweatherford in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-05-2020, 10:19 AM
  2. [SOLVED] Usine sumifs to change criteria range column based on dynamic criteria
    By Luiscarlos in forum Excel General
    Replies: 5
    Last Post: 11-19-2020, 09:33 AM
  3. Replies: 4
    Last Post: 04-02-2019, 12:22 PM
  4. [SOLVED] Split to Workbooks based on a Column followed by Worksheets based on another criteria
    By jaredli in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-26-2018, 03:27 AM
  5. Replies: 2
    Last Post: 10-10-2015, 02:22 AM
  6. [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
  7. Extracting data from column based on specific criteria and pasting into new column
    By vanvalkenburg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2012, 10:26 PM

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