+ Reply to Thread
Results 1 to 7 of 7

Sumif a range based on multiple parameters

  1. #1
    Registered User
    Join Date
    12-15-2011
    Location
    Austin, Tx
    MS-Off Ver
    excel 2007
    Posts
    4

    Sumif a range based on multiple parameters

    I need to be able to calculate guests by hour by day only using start and end date and time.

    I have been looking at countif and sumif but they seem to want to use the column to be counted in the compare. I want to count a different column than I am comparing. I need to count visitids where the date and time columns of data agree with the date and time of the created.

    What I have done is create columns of hours from 0-23 and rows for each day.

    Hours
    Date 0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
    6/5/2014
    6/6/2014
    6/7/2014
    6/8/2014


    The data I have looks like:
    globalid visitid subvisitit visitstart_date VisitStarthour visitend_date VisitEndhour
    1 99 999 6/5/2014 4:00 6/9/2014 4:00
    2 100 1000 6/5/2014 6:00 6/10/2014 6:00
    3 101 1001 6/6/2014 12:00 6/11/2014 12:00
    4 102 1002 6/7/2014 2:00 6/12/2014 2:00
    5 103 1003 6/7/2014 14:00 6/13/2014 14:00
    6 104 1004 6/7/2014 18:00 6/14/2014 18:00
    7 105 1005 6/8/2014 7:00 6/14/2014 7:00
    8 106 1006 6/8/2014 8:00 6/14/2014 8:00
    9 107 1007 6/8/2014 9:00 6/14/2014 9:00
    10 108 1008 6/9/2014 10:00 6/14/2014 10:00

    What I need to do is count the range of visitid

    if

    visitstart_date > = the date in the row
    and
    visitstarthour <= the hour in the column
    and
    visitend_date > = date in the row
    and
    VisitEndhour > = the hour in the column

    datacalctest.xlsx

    I have uploaded the workbook. The data I need is on the worksheet wanted.

    I am trying to figure out how to do this programatically so I can pull my historical data out.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,293

    Re: Sumif a range based on multiple parameters

    Hi and welcome to the forum

    Have you tried COUNTIFS() or SUMIFS() - note the plural (first introduced in XL 2007)
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2016 (work) and 2013 (home)
    Posts
    1,164

    Re: Sumif a range based on multiple parameters

    See if this works for you. It uses COUNTIFS as Richard suggested.
    datacalctest.xlsx
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  4. #4
    Registered User
    Join Date
    12-15-2011
    Location
    Austin, Tx
    MS-Off Ver
    excel 2007
    Posts
    4

    Re: Sumif a range based on multiple parameters

    You were exactly right with the plurals. Thank you.

  5. #5
    Registered User
    Join Date
    12-15-2011
    Location
    Austin, Tx
    MS-Off Ver
    excel 2007
    Posts
    4

    Re: Sumif a range based on multiple parameters

    Thank you very much for the code example. I was wondering how to tie them together. I really appreciate the work.

  6. #6
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2016 (work) and 2013 (home)
    Posts
    1,164

    Re: Sumif a range based on multiple parameters

    What do you mean by tie them together? What is the end goal? What are you trying to achieve?

  7. #7
    Registered User
    Join Date
    07-01-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Sumif a range based on multiple parameters

    hi
    ifthenelsenull,

    please check,

    hoped can meet with your expectations.
    so, you can check guests by hour and by day,
    without create columns of hours from 0-23 and rows for each day.

    regards,

+ 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. Date calculation based on multiple parameters
    By koudesak in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 10-03-2011, 10:18 AM
  2. Max based on multiple variables with multiple parameters
    By Latszer in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 10-09-2009, 03:39 AM
  3. VBA Function Based on Parameters & Data Range
    By crs245 in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 08-21-2008, 02:37 PM
  4. Query and sum based on multiple parameters
    By minkus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2007, 04:34 PM
  5. Count cells in one range based on parameters in another range
    By dave roth in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-29-2005, 03:06 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