Multiple SUMIF Criteria formula results in #Incorrect Argument Set or #Unparcible.

I have a formula that works with one Criteria range however it is not working. The Formula that I am using is.

=SUMIFS([Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "7. Paid" + SUMIFS([Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "8. Commissioned"))

I have also tried.

=SUMIFS([Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "7. Paid", ([Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "8. Commissioned"))

And Finally Tried

=SUMIFS([Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "7. Paid", "8. Commissioned")

All which gives me an error. Someone please help direct me to finding the correct Formula?

Find more posts tagged with

Formulas

Accepted answers

KPH

If your data looks like this

Then the formula below will SUM all the deal values for the rows shown in yellow:

=SUMIFS([Deal Value]:[Deal Value], [Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], OR(@cell = "7. Paid", @cell = "8. Commissioned"))

This part in bold is basically saying Contract Stage can be either 7 or 8 to be counted.

Your first formula was also super close. You just needed to move one parenthesis and add in the column to sum. This also works (but the one above is shorter and easier to reuse as you aren't repeating yourself.

=SUMIFS([Deal Value]:[Deal Value], [Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "7. Paid") + SUMIFS([Deal Value]:[Deal Value], [Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "8. Commissioned")

KPH

That might not seem like it, but it is a good sign! It suggests the column names in the formula do not match the column names in the data.

Can you delete [Deal Value]:[Deal Value]

Then, with your cursor in the same place that text used to be, select some cells in the Deal Value column.

You will see a dotted blue border around the cells you select and the formula will update like this:

Now remove those numbers (the 1 and 2 in my example). This means that instead of just rows 1 to 2, the entire column is referenced.

The formula should then look like this and be color coded with the column highlighted.

Repeat for each column until all three show in color.

Here I am changing Inspection Type

Then I remove the 1 and 8.

Then the same for Contract Stage.

KPH

For your follow up.

You have a formula that is summing the deal value where Inspection Type is a certain value, Contract Stage is one of two values. This is good.
You want to do something with Paid Date in 2023.

Is Paid Date a date column?

Do you still want to sum the deal value?

If so, you can add an additional range and criteria to your SUMIFS.

First you need a formula to find the year from your Paid Date.

Then you put this into the SUMIFS (after an extra comma).

Like this:

=SUMIFS([Deal Value]:[Deal Value], [Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], OR(@cell = "7. Paid", @cell = "8. Commissioned"), [Paid Date]:[Paid Date], YEAR(@cell) = "2023")

All comments

KPH

How about this (move the parenthesis in bold from the end to the end of the first SUMIFS)

=SUMIFS([Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "7. Paid") + SUMIFS([Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "8. Commissioned")

But I think you are also missing the thing to sum, another column before Inspection Type

LuminaVPSales

Also have tried this... =SUMIFS([Deal Value]:[Deal Value], [Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], "7. Paid", [Contract Stage]:[Contract Stage], "8. Commissioned")

KPH

This looks better but it will only sum Deal Value if Contract Stage is 7. Paid and Contract Stage is 8. Commissioned which is probably not possible.

I think you need to include an OR here.

Are you looking to sum the Deal Values of all rows where the Inspection Type is Capital Needs Assessment and the Contract Stage is 7. Paid or 8. Commissioned?

KPH

If your data looks like this

Then the formula below will SUM all the deal values for the rows shown in yellow:

=SUMIFS([Deal Value]:[Deal Value], [Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], OR(@cell = "7. Paid", @cell = "8. Commissioned"))

This part in bold is basically saying Contract Stage can be either 7 or 8 to be counted.

LuminaVPSales

Thank you for the quick help! I tried both of the formulas above and it seems to give me an error still.

Also

Your Question: Are you looking to sum the Deal Values of all rows where the Inspection Type is Capital Needs Assessment and the Contract Stage is 7. Paid or 8. Commissioned?

My Answer: I am actually looking to calculate the sum where the inspection type is Capital Needs Assessment and the Contract Stage is 7. Paid AND 8. Commissioned.

KPH

Can you share a screen shot of the data so I can see how the Contract Stage can be both 7. Paid and 8. Commissioned? Is it a multi-select dropdown list?

LuminaVPSales

It is a dropdown list with "Allow Multiple Values per cell" not selected.

Due to some sensitive information and per our contracts I unfortunately cannot show screen shots.

KPH

Can you mock up a table with some fake data (like the one I posted), or redact a screen shot? If the dropdown does not allow multiple values per cell, I am unsure how a row can have a Contract Stage that is both 7. Paid and 8. Commissioned.

If it was one or the other (like the mock up I posted above) then you could do the SUMIFS with the OR to calculate the total deal if one or the other.

LuminaVPSales

Here is a screenshot.

Also when I use this

=SUMIFS([Deal Value]:[Deal Value], [Inspection Type]:[Inspection Type], "Capital Needs Assessment", [Contract Stage]:[Contract Stage], OR(@cell = "7. Paid", @cell = "8. Commissioned"))

It comes back as #UNPARSEABLE

KPH

Thank you @LuminaVPSales

That image is really helpful. You do not have any rows with both 7. Paid and 8. Commissioned in the Contract Stage column. It really does look like you want the sum the deal value for all rows if the stage is 7. Paid OR 8. Commissioned.

So that formula should work for you. It will sum all the deal values except rows 13 and 14, in your example. Can you copy and paste the formula from your sheet into here so I can see exactly what your formula looks like?

LuminaVPSales

Here is the screenshots of the formula in the summary section.

And I get this.

KPH

I can't see any difference there (but I don't have my glasses with me so am struggling a little!). Does yours color-code like this:

The column names are colored the same as the borders around the columns in the data.

LuminaVPSales

I do not have any color coding showing like yours. it is all in black text. I do have the () in purple and blue as yours is.

KPH

That might not seem like it, but it is a good sign! It suggests the column names in the formula do not match the column names in the data.

Can you delete [Deal Value]:[Deal Value]

Then, with your cursor in the same place that text used to be, select some cells in the Deal Value column.

You will see a dotted blue border around the cells you select and the formula will update like this:

Now remove those numbers (the 1 and 2 in my example). This means that instead of just rows 1 to 2, the entire column is referenced.

The formula should then look like this and be color coded with the column highlighted.

Repeat for each column until all three show in color.

Here I am changing Inspection Type

Then I remove the 1 and 8.

Then the same for Contract Stage.

LuminaVPSales

THAT WORKED!! Thank you, THANK YOU!! Have a wonderful rest of your day!!

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.