How to use SUMIFS if a column is not null

Ines Kuo

Below is the formula

=SUMIFS({DOT 2023 Design OPP}, {DOT 2023 Range 2}, "LV MOSFET", {DOT 2023 Range 3}, "Design Opportunity", {DOT 2023 Range 5}, IF(CONTAINS("AO", @cell),true))

I got zero dollar. Our part number always starts with "AO". Please help

Nic Larsen

Try:

=SUMIFS({DOT 2023 Design OPP}, {DOT 2023 Range 2}, "LV MOSFET", {DOT 2023 Range 3}, "Design Opportunity", {DOT 2023 Range 5}, CONTAINS("AO", @cell)

Ines Kuo

@Nic Larsen, still return with zero dollar.

Nic Larsen

Have you tried your formula without at the Contains Range and Condition to test whether its working before refining? Are you get an error message or just a zero amount? Are all your cross sheet ranges the same length?

Can also try with the HAS function instead of Contains:

=SUMIFS({DOT 2023 Design OPP}, {DOT 2023 Range 2}, "LV MOSFET", {DOT 2023 Range 3}, "Design Opportunity", {DOT 2023 Range 5}, HAS(@cell, "AO")

Ines Kuo

Yes, if I remove the Contains Range and Condition then I get the total sum. However, I only want to sum total if the DOT 2023 Range 5 column has Part Number starting "AO".

Nic Larsen

Hmm strange, it works in testing for me. Do you have a screenshot that you can show of part of the column that contains AO. And just be safe, it's not A0 vs AO - a number vs letter?

Ines Kuo

This is list of part number starting with letter AO.

Nic Larsen

That looks straight forward. I updated my example with inbound/outbound links from another sheet and it's still working fine. I'm a bit stumped. My only other two thoughts or maybe suggestions:

1. Try a COUNTIF formula just to see how many cells in that column it can count that Contain AO. And see if that is picking up a count even if it's a small sample. If it isn't recognizing it either then….
2. I'd create a new column next to it and try =VALUE(cell containing AO) and run that down a few rows and try again using that column as the source of AO. Maybe the source from the cell linking is causing issues…