Why do I have a formula that is not working in sheet summary, yet is working in another sheet.

Max Corbin

I have a column with some rows marked as "Active /Won" in the "Project Status" column. I want to sum all of those values from the corresponding column "Project $ Value". I have done this successfully in another sheet, but I cannot get this formula to work on my sheet summary...

In my sheet summary I get "#invalid operation" with the below formula.

=SUMIF([Project Status]:[Project Status], "Active /WON", [Project $ Value]:[Project $ Value])

In my sheet that works I have the below formula.

=SUMIF({Project Status}, "Active /WON", {Project $ Value})

Help Please!

Kelly Moore

What happens if you change your Sum column to EAU? To the SUMIFS plural original version (ensure correct syntax), let's add the ISNUMBER criteria.

=SUMIFS([Project $ Value]:[Project $ Value],[Project $ Value]:[Project $ Value], ISNUMBER(@cell), [Project Status]:[Project Status], "Active /WON")

Kelly Moore

Hey @Max Corbin

To clarify, the sheet you are currently summarizing is the sheet that contains the data you are referencing? Do you have any errors that are present in the Sum column?

Max Corbin

Yes, that is correct. As far as errors, there are some values that are blank and some that are zero from a formula that are the two columns to the left multiplied together but no values to multiply together entered in those two columns. I am not sure how to tell if they are considered errors.

Kelly Moore

Just for grins enter your formula as a SUMIFS. It really about starting from scratch.

Do this by clicking in the cells to bring in the column references and deleting the row number, rather than manually typing the column names. Copy paste the Active/Won into your formula, between the quotes. I'm trying to eliminate any spelling/capitalization issues.

=SUMIFS([Project $ Value]:[Project $ Value], [Project Status]:[Project Status], "Active /WON")

If this doesn't work, is it possible that you can get a screenshot of your formula so I can see the colors? Also, if you could get a screenshot of the data so I can see your column names.

Kelly

Max Corbin

I tried the way you asked and got the above.

Below is how I had my formula...

Kelly Moore

Try adding @cell to your = "Active /WON" term in either the SUMIF or SUMIFS. As an fyi, you can always use the plural version of the functions, even when you have only a single criteria. My personal preference is to always use the plural version in case I later need to add additional criteria.

Using your formula, here's what I mean

=SUMIF([Project Status]:[Project Status], @cell="Active /WON", [Project $ Value]:[Project $ Value])

I'll be aggravated if that fixes it. Relieved but aggravated I didn't think of that soon.

Kelly

Max Corbin

Tried both the singular and plural SUMIF version of the @cell="Active /Won" and I still have

Kelly Moore

Hey Max

You are using SUMIFS plural function but the singular syntax.

Have you tried any other text instead of Active/won? Just to test to see if something strange is going on with that data set

Max Corbin

Just tried "Closed" and "On Hold". They did not work either. Also, I created a column formula for "Project $ Value" and deleted any cells with text. I still does not work.

Kelly Moore

What happens if you change your Sum column to EAU? To the SUMIFS plural original version (ensure correct syntax), let's add the ISNUMBER criteria.

=SUMIFS([Project $ Value]:[Project $ Value],[Project $ Value]:[Project $ Value], ISNUMBER(@cell), [Project Status]:[Project Status], "Active /WON")

Max Corbin

The below formula worked:

=SUMIFS(EAU:EAU, EAU:EAU, ISNUMBER(@cell), [Project Status]:[Project Status], "Active /WON")

Also, it automatically took away my [ ] brackets. I entered [EAU]:[EAU] and it came back EAU:EAU. Is that normal? Now, how do we get it to work for Project $ Value?

Max Corbin

Also I tried to sum the entire column for Project $ Value and it would not work but it did for EAU column.

Max Corbin

OK, good news. I created a filter in the Project $ Value column for all values that are not a number and there was one that had text in it. Now it works!!! I had already done this with the other two columns but not this one. Thank you for your help!

Kelly Moore

phew! I knew it had to be a number thing in that SUM column

Also, to answer your question about the square brackets. Square brackets are only required in column names when the name contains a space, special character, or number. If it's just a plain word, no brackets are required.

Sorry this took so long to figure out. Thanks for hanging with me.