Cant figure out Summing 2 columns based on text in another

I'm trying to sum up two budget columns, based on the budget type in another column. For some reason I keep getting errors and can not figure this out.

=SUMIF(Program:Program, "BDM", [PO Raised]:[Planned, not POd])

Program Column = Text/Number = "BDM"

PO Raised & Planned, not POd columns are also text/number columns that have numbers in them. I'm simply trying to add up all the numbers in both columns for rows that indicate BDM in the Program column.

Any advice?

Find more posts tagged with

Formulas

Accepted answers

All comments

bisaacs

Hey @HSabin,

Did you copy/paste that formula from your sheet? It looks like you closed off the parenthesis too early. Could you provide a screenshot of your sheet so we can see the layout of your columns?

HSabin

hi @bisaacs Thanks for the reply.

Here is the pasted formula, I mis-typed it in my earlier post:

=SUMIF(Program:Program, "BDM", [PO Raised]:[Planned, not POd])

I can't share screenshot as its a confidential budget document. As for layout of columns... what do you mean exactly? And how would that affect a formula I'm adding as a sheet summary? I have multiple other sheet summaries that are calculating perfectly, I just can't figure out what I'm doing wrong with this one.

I have columns for things like:

PO#
Program
Supplier
Project
Description
Quarter
PO Rasied (budget numbers)
Planned, not POd (budget numbers)
etc... there are more columns but all are similar to these.

Is that helpful at all?

bisaacs

Hey @HSabin,

I didn't realize you were trying to do a sheet summary. I think the issue is you are trying to use two different size ranges in the formula. If I just make the sum range one column it works for me.

HSabin

Hi @bisaacs - appreciate it. I can't have all that info in one column. It has to be separated.

Also - I've summed two columns (the same two) in other calculations on the sheet summary and its worked fine. I think it may be a glitch. I am not sure.

bisaacs

Hey @HSabin,

Could you share the formula that does work calculating the two columns? Is it formatted the same way?

I was able to make it work by creating a dummy column next to the Program column (that is hidden) and including it in the eval range:

=SUMIF(Program:helper, "BDM", [PO Raised]:[Planned, Not POd])

Hope this helps!

HSabin

HI @bisaacs ,

The basic total sum worked fine: =SUM([PO Raised]:[Planned, not POd])

Thank you for the suggestion about a helper column, but I'm not following what you did with the helper column. I already have a "program" column that would include "BDM" - what are you populating in a helper column?

bisaacs

Hey @HSabin,

I have to remember what I did, but I think I just left the helper column empty. Since the issue was the two ranges in the formula weren't the same size, I included the helper column so both ranges were the same size.

When I use the formula with and without the helper column, it works with the helper, but it doesn't work without it.

Although doing it that way it would sum the numbers in the PO Raised column but not the other.

Actually, I see what's wrong. It appears you'll have to make the helper column equal Program@row so that it duplicates what's entered into the Program column:

=Program@row (Make this a column formula)

Then (after you hide the helper column), in your sheet summary, use the formula I provided previously to calculate the SUMIF:

=SUMIF(Program:helper, "BDM", [Planned, not POd]:[PO Raised])

This should sum up the columns you're requiring!

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.