SUMIF(CONTAINS...

Marilu M

I'm trying to gather info from another sheet but keep getting the "Unparseable" error message. Basically, I want to add the total amounts of any rows that contain "1st" as the district. The column that has the district information is a multi-select value hence why the contains. The current formula is set as:

=SUMIF( CONTAINS("1st"{FY22 NAI Funding Application Range 2})), {FY22 NAI Funding Application Range 3}

Any assistance would be appreciated.

Genevieve P.

Hi @Marilu M

If you're looking in a multi-select column for a specific value, I would actually use HAS Function instead. It looks for a direct match.

You'll want to first list the column/range, then use @cell as the second range within your HAS function. This is so that it looks into each cell of that column to see if it has that value.

Try this:

=SUMIF({FY22 NAI Funding Application Range 1}, HAS(@cell, "1st"), {FY22 NAI Funding Application Range 2})

Cheers!

Genevieve

Marilu M

I tried this version, and I am getting 0:

=SUMIF(CONTAINS("1st", {FY22 NAI Funding Application Range 1}), {FY22 NAI Funding Application Range 2})

Genevieve P.

Hi @Marilu M

If you're looking in a multi-select column for a specific value, I would actually use HAS Function instead. It looks for a direct match.

You'll want to first list the column/range, then use @cell as the second range within your HAS function. This is so that it looks into each cell of that column to see if it has that value.

Try this:

=SUMIF({FY22 NAI Funding Application Range 1}, HAS(@cell, "1st"), {FY22 NAI Funding Application Range 2})

Cheers!

Genevieve

Marilu M

@Genevieve P. It worked!! Thanks so much!!!

Genevieve P.

No problem! 🙂

earl_bennett

@Genevieve P. I'm doing something very similar and it works when I hard code the value in the HAS statement. But, when referencing a value in a cell it doesn't.

Using the example above: HAS(@cell, "1st") works, but HAS(@cell, [1]2) does not.

Note... The value in cell [1]2 is populated with a cross sheet formula the capture the names of our PM's.

Genevieve P.

Hi @earl_bennett

When you say it doesn't work, are you receiving an error or an incorrect result?

earl_bennett

Sorry, @Genevieve P. ... Not sure why, but it appears to be working now. It wasn't throwing an error just not returning the expected result. So weird, but thank you!

Genevieve P.

Hi @earl_bennett

Glad to hear it's resolved! 🙂

SkiPatrolScott

@Genevieve P. not sure if I am misunderstanding the use of HAS..

=SUMIF(Route:Route, HAS(@cell, "GAZ"), [Total Used]:[Total Used])

I am trying to use this formula to sum all entries in Total Used where GAZ is part of the route, GAZ EX (Mt Allen) GAZ EX (No Name).... but unless I type the entire word it comes back as 0, I tried contains as well, and got 0. There will be at least 3.. and I am sure I will need variations of this formula for other routes....

On the same thread, I would like to sum all the entries that do not have GAZ as part of the route.

Genevieve P.

Hi @SkiPatrolScott

HAS will only search for exact matches, so only rows that have "GAZ" selected specifically, without any other text in that same selection. This means searching for HAS(@cell, "GAZ") excludes "GAZ EX (No Name)"

You would want to use CONTAINS in this instance, to see if the string contains GAZ along with other words. Try:

=SUMIF(Route:Route, CONTAINS("GAZ", @cell), [Total Used]:[Total Used])

Then I would suggest for your second formula, simply using SUM to get the entire column and subtract your previous formula from the total:

=SUM([Total Used]:[Total Used]) - [SUM Hand Shots]#

If the CONTAINS function still comes back as 0, try SUMming the column without any criteria:

=SUM([Total Used]:[Total Used])

This checks to see if the values in that column are being read as text or numerical. If this general SUM is 0, then the numbers are being seen as text and cannot be added together. Can you clarify if there's a formula in this column? Or are the numbers being input manually?

Cheers,

Genevieve

SkiPatrolScott

thanks @Genevieve P. I think I was not placing the contains("Gaz", @cell) in the correct order, yes the formula is working now....

Genevieve P.

Glad to hear it! 🙂

Yes, HAS and CONTAINS have opposite syntax, haha. I've been caught by that before.