SUMIF error when no data exist

[Chris McGlothen]

This might be a little hard to explain but what I am trying to do is Sum UP Values on a Worksheet and average what we sell for the month and use it as a percentage to base what we might sell that month based off of History. The problem is some Items have no history. For the lines that have no history I would like for it to do a Vlookup an the sheet called "Weighted Table" and times that percentage by Column "Y". I just don't know how to make it perform the vlookup when it doesn't find the Part Number in the list? Any help would be greatly appreciated. Line 23 (Apple21) is an item that has no history so it creates the dreaded #DIV/0! error. I have attached a dummy sample of the issue. Thanks,

[CK76]

Nest your formula in IFERROR and use vlookup in value if error part.

So...
=IFERROR(YOUR FORMULA,$Y23*VLOOKUP(TEXT(Worksheet!B$1,"mmm"),'Weighted Table'!$A$1:$B$12,2,0))

[zbor]

You don't need all SUMIFS when you have total in dana sheet.

Try this in B23:

Please Login or Register  to view this content.

[JohnTopley]

In B2

=IFERROR(ROUND(SUMPRODUCT((Data!$B$2:$B$197=Worksheet!$A2)*(Data!D$2:D$197))/(SUMPRODUCT((Data!$B$2:$B$197=Worksheet!$A2)*(Data!$D$2:$O$197)))*$Y2,0),VLOOKUP(TEXT(B$1,"mmm"),'Weighted Table'!$A$1:$B$12,2,0)*100)

copy across and down

[Kaper]

How about (for cell B2 and copy down/right):

Please Login or Register  to view this content.

[bebo021999]

Try in B2:

Please Login or Register  to view this content.

Copy down and accross.

[Chris McGlothen]

bebo021999, that seems to work. I made the "Y" absolute. The only problem is how do I make it round the result on the error?

[bebo021999]

Just moving it out:

Please Login or Register  to view this content.

[Chris McGlothen]

Thank you to all that helped!!! Bebe021999 that worked like a charm!!!