Thank you very much for your effort, Klaus.

I copied your formula all the way down in col D and it was effective in detecting the change in the name of the vendor, however it gave the "#VALUE!" every time as the answer.

I also noticed that the sumproduct part of the formula will never capture the values in col C, 100+250+578=928 for vendor A of the sample) which is what I need. It may be because the formatting of my message was altered.