Attempting to add similar values with Sumif

I have a sheet that could list the same values multiple times as they are being populated via a Zap from Google Sheets. So that I can find a true total value of the Job Name, I am counting the times that they appear on the sheet and then dividing the Total Amounts of Sale for a Job by the number of instances on the sheet, see Average Sales Value. But it gets complex because I also need to add an average sale of Change Orders together with the original Job Name, i.e. Ye[Job #1887] and Ye[Job #1887] CO2. I am stuck on how to add the two averages together to get a grand total for the job.

So, for instance, I want to add the Average Sale Value of rows 7 and 8. I am stuck.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hey @Barb R

You can use the CONTAINS function to see if a cell contains a specific value. This means you can SUM together a total value for 1887, even though there are other pieces of text along with the number.

Try something like this:

=SUMIFS([Job Name]:[Job Name], CONTAINS("1877", [Job Name]@row), [Amount of Check]:[Amount of Check])

Now that has the 1877 hard coded into the formula, so it wouldn't work in this sheet as you need it to be dynamic for each row. What I would suggest in this case is to set up a second sheet as your Metric sheet. One column would have all the Job Numbers to reference, then you can have the same SUMIFS but referencing the cell in the row instead of "in quotes":

=SUMIFS({Job Name Column}, CONTAINS([Job Name]@row, @cell), {Amount of Check Column})

Cheers,

Genevieve

Barb R

Thank you. The problem is, I cannot sumif all of the matching job numbers, I need to separate the CO from the original because they have different amounts and cannot be averaged together. So, I need to add them as two distinct values. Your answer was useful however for potentially other situations. I was hoping to do a different sumif for those that matched the Job Number but had the CO and a wildcard. Looking at your formula, I may be able to figure that out. I was trying to use the Contain function, but it wasn't working.

Genevieve P.

Hi @Barb R

In this case I do think a separate sheet would be the best way to get your data together. This way you can list each individual Job Name down one column, but also group together the ones you want to average. You can have individual averages on each row (pulled using cross-sheet references to each individual job number), but then total averages based off of that data calculated in this summary sheet. Does that make sense?

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.