Sum of missing values

[Sybille]

Hello Forum,

I need a formula that would sum all the values corresponding to the data in a range that are missing in another range.

You will see exactly what I need in the attached file.

Thank you all and good day!

[Ace_XL]

Try

=B7-SUMPRODUCT(SUMIFS(B2:B6,A2:A6,A9:A11))

[Pete_UK]

Try this formula in B13:

=SUMPRODUCT(--(ISERROR(MATCH(A2:A6,$A$9:$A$11,0))),B2:B6)

Adjust the ranges to suit your real data.

Hope this helps.

Pete

[WideBoyDixon]

Trivially, I guess it's also:

Please Login or Register  to view this content.

But you're not using a formula to find the values in B9:B11 (VLOOKUP would work for you here).

WBD

[Sybille]

Hello there,

All of your solutions work, thank you so much.

The winner is WideBoyDixon though. First I felt embarrassed and ashamed and then I laughed so hard at myself with the simplicity of the formula. In French we have something that goes like "Why would I do things simple when I can do them complicated...", and I guess today is THE day I will do everything complicated!

Trivially Solved!