+ Reply to Thread
Results 1 to 3 of 3

SumIf to populate sheets and tables

  1. #1
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Arrow SumIf to populate sheets and tables

    Hi all,

    I'm looking for a dynamic formula which populates tables in sheets 'Site 1', 'Site 2', and 'Site 4' .
    The sheet name (Site #) is in cell A1 of each sheet to populate. Each sheet has 2 tables, either Northbound and Southbound, or Eastbound and Southbound (depending on the site). The direction for each table is the cell above that table (i.e. A2 for the first table in a sheet and A33 for the second table)
    The 'Clean' sheet has all the data for each site, day and direction, and the hours of the day are along the columns.

    I need a SumIf formula (or anything really) that populates each table depending on the time, day, direction, and site number.

    Any help would be greatly appreciated!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: SumIf to populate sheets and tables

    Try

    in C5

    =SUMIFS(INDEX(Clean!$G:$AD,0,MATCH($A5,Clean!$G$1:$AD$1,0)),Clean!$A:$A,$A$1,Clean!$C:$C,$A$3,Clean!$E:$E,B$4)

    or

    =SUMPRODUCT((Clean!$G$2:$AD$103),(Clean!$A$2:$A$103=$A$1)*(Clean!$C$2:$C$103=$A$3)*(Clean!$E$2:$E$103=B$4)*(Clean!$G$1:$AD$1=$A6))
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: SumIf to populate sheets and tables

    Would it not be easier to analyse the data (since that is I guess you want to achieve) from sheet clean with a pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. Replies: 3
    Last Post: 11-10-2018, 06:59 AM
  2. Replies: 2
    Last Post: 06-21-2017, 06:32 PM
  3. [SOLVED] Search Columns from Multiple Sheets If Match Populate Value from 1 of the sheets
    By kellyjo7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-08-2014, 05:52 PM
  4. Replies: 6
    Last Post: 03-12-2014, 09:23 AM
  5. Populate tables by category
    By mk0708 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2013, 02:16 AM
  6. Replies: 3
    Last Post: 12-17-2012, 01:12 PM
  7. Replies: 0
    Last Post: 08-17-2012, 02:02 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