Need a SUMIF formula Contains or HAS for Category fund to total Budget in referenced sheet.

Linda F

I need a SUMIF formula that will sum all Cat's in the identified operating budget. I thought I could use the CONTAINS or HAS but its not working. The category (not purple Budget Category) will have a number after but I don't need that number. The code in Cat is the letter (R, TO, E, TI) I need so it sums the revised operating budget. Below is what I started but it doesn't work.

=SUMIF({FY2023-2024 Working Budget Phase I SP Range 1}, CONTAINS({FY2023-2024 Working Budget Phase I SP Range 3}, "R", {FY2023-2024 Working Budget Phase I SP Range 4}))

Genevieve P.

Hi @Linda F

My apologies! I used the wrong syntax for CONTAINS... I'm so used to working with HAS, which uses @cell first.

Try this:

=SUMIFS({Budget Column}, {CAT Column}, CONTAINS("R", @cell), {Account/SA # Column}, [Account/SA]@row)

Genevieve P.

Hey @Linda F

CONTAINS looks like the right way to go!

This is the structure I would suggest:

=SUMIF({CAT Column}, CONTAINS(@cell, "R"), {Budget Column})

So if I'm understanding your ranges correctly:

=SUMIF({FY2023-2024 Working Budget Phase I SP Range 1}, CONTAINS(@cell, "R"), {FY2023-2024 Working Budget Phase I SP Range 4})

Let me know if that works!

Cheers,

Genevieve

Linda F

I think it would work but I must leaving out one more thing, the Account/SA #. So there are 2 criteria to lookup. One is the account/SA # (on 1 sheet) and the other is the Budget Category (reference sheet - second sheet). Once we have that then Sum. The formula you gave me works but I also need to consider the Acct/SA we are summing for.

Genevieve P.

Hi @Linda F

Thanks for clarifying! If you have two ranges and criteria then you'll want to use SUMIFS with an S. The structure for this then has the column to SUM at the front of the formula, like so:

=SUMIFS({Budget Column}, {CAT Column}, CONTAINS(@cell, "R"), {Account/SA # Column}, [Account/SA]@row)

See: SUMIFS Function

Cheers,

Genevieve

Linda F

So the formula works but it doesn't pull in cat's that contain the letter "R", "E", "TO", "TI". BUT if I put in the correct categories such as R27 then it pulls it in - instead of contains - could there be another function that is not so specific to the cat number in the cat code?

Genevieve P.

Hi @Linda F

My apologies! I used the wrong syntax for CONTAINS... I'm so used to working with HAS, which uses @cell first.

Try this:

=SUMIFS({Budget Column}, {CAT Column}, CONTAINS("R", @cell), {Account/SA # Column}, [Account/SA]@row)

Linda F

It Worked!! How awesome !!! That was the piece that worked! Thank you so much !