How can I total numbers across two sheets?

Hi everyone,

I need some guidance on setting up a formula.

I've got two separate sheets with consistent fields including name and position/title.

I want to total numbers from one sheet using the name and position fields as parameters.

In the past, I've used an average formula (pasted below) with success. I'm looking to duplicate this, but am looking to add and not average numbers.

I tried a similar formula with "sum" instead of "avg", but it didn't yield the results I was looking for.

Any guidance would be appreciated.

Thank you!

=IFERROR(AVG(COLLECT({Tech Manager RTG}, {Position}, POSITION@row, {Name}, NAME@row)), "")

Find more posts tagged with

Accepted answers

All comments

Michelle Choate 2

You would just add the sums together. So SUMIFS(Parameters from Native Sheet) + SUMIFS(Using references from 2nd sheet)

Jennifer Kurtz

Hello!

Can you share a bit about why the formula with "sum" instead of "avg" did not yield what you were looking for? Is it that you'd like to total data found in two different sheets?

To SUM data that meets multiple criteria, I would use the SUMIFS function. (But, you've achieved the same result with your SUM(COLLECT, and could certainly stick with that if you prefer!) To pull data from two different sheets, you could combine two formulas together, e.g.,

=SUMIFS({Sheet1-Tech Manager RTG}, {Sheet1-Position}, Position@row, {Sheet1-Name}, NAME@row)+SUMIFS({Sheet2-Tech Manager RTG}, {Sheet2-Position}, Position@row, {Sheet2-Name}, Name@row)

Does this help?

Andrew Ryback

Hi! I was able to figure this out. The issue was that my source cell was producing text instead of a number, so my formula was spitting back 0's, even though there were values to count.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.