Calculating SUMs based on Criteria 1 and 2

I am trying to calculate the total cost if Agency = X and if Status = Converted, take Total Spend - i've tried it a few ways but its just returning errors

Here's my most recent one:

=IF([Total Cost for Converted]@row <> "", [Total Cost Overall]@row + IF(CONTAINS("Converted", {Status Range}), {Estimated Travel Spend}, 0), 0)

I'm also trying to find a way to display a pie chart or graph of some sort to show what agencies are under what status - is there a way to do this without making it into a report?

@Paul Newcome 😁

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

For the first bit, try a SUMIFS.

=IF([Total Cost for Converted]@row <> "", [Total Cost Overall]@row + SUMIFS({Estimated Travel Spend}, {Status Range}, CONTAINS("Converted", @cell)), 0)

For the second, are you able to provide some screenshots for context?

Cayla Davis

I just get circular reference error

=IF([Total Cost for Converted]@row <> "", [Total Cost Overall]@row + SUMIFS({GBTN Referrals Sent Range 3}, {GBTN Referrals Sent Range 2}, CONTAINS("Converted", @cell )), 0)

For the dashboard, im trying to say

Agency A = Backlog

Agency B = Production

Agency C =Pending

and put that into a Pie Chart

Paul Newcome

The formula would need to go into a cell that is not being referenced by the formula.

What would be the labels for the pie chart? The different statuses with the counts for each? Or are you meaning to have each agency as their own label?

Are you able to provide some screenshots of the source data? Mock / sample data is fine.

Cayla Davis

That doesnt seem to be giving me what i need

I need to take the Total Cost for Converted (which is actually the Agencies), and if Status on another sheet is Converted, take the values found in the Estimated Travel Spend and add up the converted agencies

=IF([Total Cost for Converted]@row <> "", [Total Cost Overall]@row + SUMIFS({GBTN Referrals Sent Range 3}, {GBTN Referrals Sent Range 2}, CONTAINS("Converted", @cell )), 0)

For the metric; they're looking for something like this
Agency A is the overall Agency, and they have 6 subagencies; but they want to break out how many are pending, converted, etc… i think i would have to make this per agency - ideally i'd like to have them in one chart but i dont think i can do that….

Paul Newcome

Sorry, but I'm not sure I follow what you mean for the formula. Do you have some screenshots for context?

For the chart, my suggestion would be to have a column for each status and then a row for each agency. Then you should be able to chart that in a stacked bar or stacked column.

Cayla Davis

Could we schedule some time together? think it might be easier

Paul Newcome

@Cayla Davis I have sent you a message on LinkedIn.

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.