SUMIFS and referencing columns on another sheet

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.

Find more posts tagged with

Formulas

Support

Sheet Summary

Accepted answers

All comments

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.

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.