Thanks:  0
Likes:  0

1. Hi Dave and JRRT,

Dave - nice one.

Just a warning, be careful here. Make sure your result is exactly as you want it.

If the SUMPRODUCT returns zero, your result will be the value in B21.

Suppose, however, that B21 is a value greater than any individual item in the list. The result might be negative if the SUMPRODUCT total returns a value greater than B21.

I read the OP's intent to be:
1. Identify (D,E) series that match criteria.
2. Subtract each result from B21.

Obviously, I may have misread the question, but these two formulae (I posted to the other thread) both work, yet can return vastly different results.

BTW, I did not get the original IF statement (array entered) to return the correct results.

Bye,
Jay

2. Hello Dave, JRR, and Jay:
since we are talking about different approaches, and Dave's progressively smaller and smarter solutions, how about the following array formula:

{=B21-SUM(IF(E3:E17=E3,D3:D17))}

Regards!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•