+ Reply to Thread
Results 1 to 4 of 4

Double conditional in a SUMIF function

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    48

    Double conditional in a SUMIF function

    I need some help. I'm try to cross analyse the national Covid-19 data base.

    I'm reasonably familiar with the SUMIF function with a single conditional like
    SUMIF(A:A, D3, H:H)

    I use it for the bulk of my analysis.

    I can't figure out the syntax to put a double conditional inside a SUMIF.

    So here is the example I'm struggling with.

    As a hypothetical example, say my spread sheet looks like this:
    Column A has the date for the data in that row.
    Column B has the two letter code for the state (e.g., NY) that the row contains.

    I have the date of interest (e.g., 20200403) stored in cell D1.
    I have the date of interest (e.g., NY) stored in cell E1.

    The data sets contains many dates and many states, but for each state and date combination, there is only one row.

    So, say I want to return the data in Column H, that is for the date in D1 and the state in E1.

    I'm guessing that if I were better at EXCEL, I could find it with a LOOKUP function, but frankly every time I try I screw up.

    Since the date/state combination are unique, then I could find the data for the following function which I can write in prose, but can't find the correct syntax for in a SUMIF; if it's possible.

    That prose statement is:
    SUM the data in Column H if the cell in Column A equals the cell D1 AND the cell in Row B equals the cell E1.

    Since the date/state combinations are unique, that function should yield just the one result in Column H that I am looking for.

    Can anyone help?

    metsci

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,733

    Re: Double conditional in a SUMIF function

    it sounds like you want a SUMIFS instead. The general formula for a SUMIFS is =SUMIFS(area or column to be summed, condition one range, condition one, condition two range, condition two, etc.)
    if you use dates you will need to include either "="&date or ">="&date or "<="&date for less than or equal to a date.
    for more help you can upload a sample workbook with sample data AND expected results, follow the instructions in the yellow banner at the top of the post.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    01-20-2013
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Double conditional in a SUMIF function

    Sambo Kid,

    Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you.

    Works perfectly. Solution is short and sweet.

    Two asides:
    1. I really appreciate the help. I am deeply involved trying to alert people to what I believe is a hole in their Covid-19 data analysis, and I didn't have time to rummage around for a solution.
    2. The data base I download lists the dates as text; literally like "20200404", so I was able to proceed without the hint you gave in your last paragraph. I will save your last paragraph for when I have to analyse a data base with more conventional dates.

    Can you teach me how to mark the thread as "Solved"?

    I'm off to continues to work the database.

    metsci

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,733

    Re: Double conditional in a SUMIF function

    Glad I could help. To mark as solved you use the thread tools dropdown at the top of this post. Good luck.

+ 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. SUMIF double counting issue
    By Tjadi in forum Excel General
    Replies: 2
    Last Post: 02-08-2018, 07:31 PM
  2. [SOLVED] Double Conditional in SUMIF
    By metsci in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-09-2014, 08:53 PM
  3. Double Sumif
    By doowop5000 in forum Excel General
    Replies: 1
    Last Post: 07-17-2009, 07:10 AM
  4. Double SUMIF
    By J&SPriest in forum Excel General
    Replies: 4
    Last Post: 07-08-2009, 04:15 AM
  5. Double SUMIF
    By garethgtt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-25-2007, 12:00 PM
  6. [SOLVED] Why does a sumif use double speech marks
    By owl37 in forum Excel General
    Replies: 1
    Last Post: 11-24-2005, 10:00 PM
  7. Sumif - double columns
    By Mats W. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2005, 12:05 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