Sum values in one column when values in another column are greater than zero

[mstrauss]

What formula(s) would I need to accomplish the following:

Base Table:
1 2 3
A 0 5 10
B 10 0 20
C 15 0 5


Result Table:
1 2 3
1 25 0 10
2 0 5 5
3 25 10 30

In the Result Table:
- the value in Row 3, Column 1 = 25 since in the base table:
Column = 1: Row A = 0, Row B > 0 and Row C > 0
Since Row A, Column 1 = 0 (exclude it)
Since Row B, Column 1 > 0 (keep it, 20)
Since Row C, Column 1 > (keep it, 5)

Sum result, 0 + 20 + 5 = 25

Thanks for any assistance on this.

[TMS]

It would probably be easier to follow if you used the "standard references" to rows and columns. Rows go across the sheet and are numbered down the left hand side. Columns go down the sheet and have alphabetic references across the top. Rows = Horizontal = Left to Right. Columns = Vertical = Top to Bottom.

What would be even better is a sample workbook with some typical data and an explanation, on the sheet, of the expected outcome and how it is calculated.

Regards, TMS

[mstrauss]

Sorry, example data spreadsheet is attached.

Hope this explains what I'm trying to do.

Thanks.

[mattbau43]

Example Data.xlsxSee attached

[mstrauss]

Thanks Matt. I know I can use the SUMIF function to get the total of Column G where the values in Column E are > 0.

For example, using =SUMIF(E5:E9,">0",G5:G9) will return 25 in my Result Table in Column1/Row3.

What I am unsure of is how do I alter this formula to get the correct sum in each unique intersection in my Result Table when I copy it across and down?

[mattbau43]

You will have to create a separate column like I did to tabulate the total and then reference that in your needed cell.

To be honest, your column and Row names make it very difficult to understand what you are trying to accomplish. I would help more but am not entirely sure what you are trying to accomplish.

Could you load some actual data? It may be a good reference to see what is needed.

[mstrauss]

Here's the actual spreadsheet I'm working on. The first tab has the Base Table data and the second tab has the Result table. I entered the SUMIF formulas in the first few cells that give me what I want to achieve, but I'm not sure how to modify them to be able to copy them down and across to fill out the Result Table.

[mattbau43]

Dogs and Cats.xlsxYou have columns and rows with multiple uses of the same name/heading. This makes it difficult for Excel and myself to decipher what is what.
I have renamed the headings. Please tell me what the result table is calculating

[mstrauss]

Sorry for the confusion. I attached a new example spreadsheet with notes in the second tab as to what I am trying to accomplish. I hope this is more clear.

[mattbau43]

I spent about an hour on this and am still working. I found a solution for part of this but my solution is extremely tedious. I am looking for a more user friendly result

[mstrauss]

Very much appreciate your help. I just discovered the INDIRECT function (I've never used it). But I think it may help here since it seems to allow you to use contents of one cell as part of a cell reference in a formula. I'm just not sure how exactly it would work within a SUMIF function.

[mstrauss]

Figured this out using INDIRECT function. Still not 100% sure I needed to use that function for this, but it worked. Revised spreadsheet is attached. Thanks to those that helped.