Results 1 to 7 of 7

Using SUMIF (or SUMIFS) with HLOOKUP

Threaded View

  1. #1
    Registered User
    Join Date
    01-26-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Using SUMIF (or SUMIFS) with HLOOKUP

    In cell F2:I2 i want to return the sum of columns Qtr1 thru Qtr4, respectively with the condition that Qtr1 > 0. This means that cell F2 will return the sum of column Qtr2 for the rows that Qtr1 is >0, and so on for Qtr3 and Qtr4. I want the the calculation to be dynamic in the sense that if I change cell F1 to read Qtr2, then F2 will return sum of Qtr2 >0, cell H2 will return sum of Qtr3 for all rows where Qtr2 is >0, etc.

    The formula I have in place now sums up all columns correctly with the >0 condition, but I cannot change the lookup in cell F1 (e.g. change Qtr1 to Qtr2, Qtr3, etc) and also have the calculation lookup to data in columns B,C, D, etc.

    =SUMIF($A$2:$A$8,">0",INDEX($A$2:$D$8,0,MATCH(F$1,$A$1:$D$1,0)))

    Example Data Set:

    A B C D
    1 Qtr1 Qtr2 Qtr3 Qtr4
    2 - 1 5 10
    3 2 20 - 5
    4 34 4 5 50
    5 10 50 40 -
    6 - - 1 5
    7 6 8 9 15
    8 1 - 10 50


    Example of what I want to return:
    F G H I
    1 Qtr1 Qtr2 Qtr3 Qtr4
    2 53 82 64 120

    F G H
    1 Qtr2 Qtr3 Qtr4
    2 83 59 80



    What if i wanted to add a second set of criteria? For example I only want sums of columns where both Column A and D are >0?
    Last edited by rmj6h; 01-26-2015 at 03:58 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sumif with Hlookup
    By peacock58 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-10-2014, 04:32 AM
  2. sumifs hlookup using index and Match
    By alexcrofut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2014, 06:09 PM
  3. sumif hlookup
    By starkc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2012, 08:56 PM
  4. sumif or hlookup
    By 1rcchopper in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2011, 03:22 PM
  5. sumifs/hlookup
    By mickeygoldsmith in forum Excel General
    Replies: 49
    Last Post: 11-29-2010, 05:11 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