# IF for arrays

1. ## IF for arrays

I'm working with 2 workbooks. WB1 is an order form that lists products and quantities (via manual data entry). I want WB2 to find all of the entries for one specific product (with multiple entries) on WB1 and populate the sum of the corresponding quantities on WB2.

For example:
A B
Product Quantity
36ry38 100
32ry39 120
36ry38 50

I want WB2 to find all of the products "36ry38", sum the corresponding quantities (100+50), then produce the resulting value on WB2 (150).

What is the best way to go about performing this function?

2. ## Re: IF for arrays

=SUMPRODUCT((A1:A100="36ry38")*(B1:B100))

3. ## Re: IF for arrays

``Please Login or Register  to view this content.``
Try this

4. ## Re: IF for arrays

Thanks, both of you! I am now able to throw in yet another condition and it's working!

5. ## Re: IF for arrays

.......................

6. ## Re: IF for arrays

Now here's a curveball that I can't seem to figure out. When this formula produces "0", how can I change the "0" to a blank cell?

7. ## Re: IF for arrays

Use conditional format to make fontcolor equal to backgroundcolor if the result is 0

8. ## Re: IF for arrays

Never done that before. What is the function for that?

9. Format the cell to display blank if the value is 0

10. ## Re: IF for arrays

Or google at conditional format for different examples

11. ## Re: IF for arrays

Here's a custom format that will hide 0.

0.00;-0.00;

You can changet the 0.00 to another number format.

There are currently 1 users browsing this thread. (0 members and 1 guests)