SUMIFS and referencing columns on another sheet

MsMaryannM

I've been searching the community and couldn't find an answer so hoping someone will see this and help..

I have a data sheet and am creating a calc sheet for a report. I want to look at the reference sheet, identify one "type" and then sum all the $ for that one type. I have tried different SUMIF/SUMIFS and I cannot figure out what I'm doing wrong. Here is an example of dummy data from the reference sheet (RefSheet1). I want the sum of profits for only pens.

Dale Murphy

@MsMaryannM Not sure where you want to put the result, but this should get you most of the way there:

=IF([Product Type]@row <> "", (SUMIF([Product Type]:[Product Type], =[Product Type]@row, Profit:Profit)), "")

If you put that into a column as a column formula, you end up with what I called "Running Profit per Type" that repeats the same value whenever it encounters the product type.

dm

MsMaryannM

Thanks @Dale Murphy I am looking to use the function/formula it in a separate sheet that I have for only calculations. So the function/formula would be referencing my data sheet and pull in the result to my calc sheet.

Dale Murphy

@MsMaryannM Cool. The formula shouldn't have to change except that the range references to a remote sheet need to be reconstructed.

dm