Using SUMIF (or SUMIFS) with HLOOKUP

[rmj6h]

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?

[protonLeah]

To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[rmj6h]

See attachment for example

[FDibbins]

See if this is what you want...
for the sum by Qtr....
=SUMPRODUCT(($A$1:$D$1=F1)*($A$2:$D$8))
For the sum by columns A and D >0...
=SUMPRODUCT(($A$1:$D$1=F1)*($A$2:$D$8)*($A$2:$A$8>0)*($D$2:$D$8>0))

[rmj6h]

This does not work because I want to be able to change cells F1, F2, F3 to Qtr2, Qtr3, Qtr4 so now the lookup and criteria >0 should start with column B. This means that all sums of columns B, C, and D will be based on if column B (Qtr2) is >0.

[swstr510]

i have a data, and i want to sum all data of the sheet based on 2 searches, one criterion is to match rows, and then column,

[JeteMc]

Administrative Note:

Hello swstr510 and Welcome to Excel Forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Let us know if you have any questions.