weighted average formula for cells that do NOT match a header label

[macrorookie]

Good morning -

I have columns that reference specific states, and those weighted average formulas work great, because they're matching a specific state name (e.g., CA) with a column that includes the state name. However, there are other states in the source column that don't have headers, and I want them to go into a "Other States" column. The function should be the same, but I can't figure out how to include logic that references something not being true. I attached a spreadsheet that simply (hopefully) simplifies my specific problem. I included notes on the spreadsheet too, for the problem.

Thank you very much for any help.

[XOR LX]

Hi,

It will be problematic to achieve this sort of construction using SUMIFS. You would be better off switching to SUMPRODUCT, though since this function does not ignore text values in the sum range, you will have to adapt this range (N:N) - and therefore all of the ranges - so that it does not include e.g. the column header (Dollars):

=SUMPRODUCT((1-ISNUMBER(MATCH($M5:$M20,$C$4:$J$4,0)))*($O5:$O20=$B5)*$N5:$N20)/SUMIFS($N:$N,$O:$O,$B5)

(I chose 20 as the end range reference, though obviously there is no negative impact in increasing this to an arbitrarily larger number; the starting reference, however, should remain at row 5.)

Regards

[Kaper]

Another approach - somewhat longer formula (array one -> committed with Ctrl+Shift+Enter)

Please Login or Register  to view this content.

differece between sum for all and sum for listed

or ...

may be too simple to think of, but working idea,
Why (after filling with formulas C5:J5 - they will be filled anyway I assume) not use:

Please Login or Register  to view this content.

[macrorookie]

Kaper - that worked! I continue to be impressed by the knowledge of people on this forum -- and their willingness to help others. Thank you!