How to evaluate multiple fields and if criteria is met, post appropriate data

I apologize for the awkward title, but I really don't know how else to briefly state what I want to accomplish.

I hope I don't use up all the internet ink here...

A_________B________________C______________D_______________E
1__|_TYPE OF ANIMAL__|__QUANTITY__|_TYPE OF ANIMAL__|__QUANTITY
2__|______Dog________|____7________|______Pig__________|______0
3__|______Bird________|____5________|______Cow_________|______3
4__|______Cat________|____ 4________|______Goose________|_____9

*(RANGE NAME OF B2:B4, D2-D4 = REQUEST)

If the value of B2:B4 or D2:D4 = Cat, then place the quantity listed in Z4. If B2:B4 or D2:D4 does not equal Cat, then leave Z4 blank (preferred) or display zero.

X_________Y_______________Z
1__|__TYPE OF ANIMAL_|__QUANTITY
2__|_____Dog________|___________
3__|_____Bird________|___________
4__|_____Cat________|___________
5__|_____Pig________|___________
6__|_____Cow________|___________
7__|_____Goose______|___________
8__|_____Donkey_____|___________

*(RANGE NAME OF Y2:Y8 = INVENTORY)

The REQUEST area allows the customer to enter the six types of animals they selected and the quantity of each they need.

The INVENTORY area lists all animals available. Since a customer may only select six different animals (but can choose fewer), not every animal in this list will have a value listed beside it.

The formula that was given to me is cumbersome and bulky. I told the person I am helping that there has to be a cleaner method. The current formulas (found in cells Z2:Z8) are set up like this: (this example would be from Z2 and is expanded for easier reading)

Please Login or Register  to view this content.

This example formula probably doesn’t look too messy, but consider that this is a tiny example. The real spreadsheet has an inventory that lists 73 items. The problem was shared with me because the original formula had too many nested IFs and the current workaround is, in my opinion, gross. See for yourself…

Please Login or Register  to view this content.

If my ASCII art spreadsheet is too hard to work with, please let me know and I will take some screenshots and post those instead.

Thanks for your time.

Maybe:

=SUMIF('TY 11 Class V Justification'!$Q$16:$S$24,'Class V Forecast by Month'!$A3,'TY 11 Class V Justification'!$R$16:$T$24)

you can hide any 0 value by custom formatting the result cells as: 0;-0;;@

Try attaching an actual workbook sample so tests can be done and presented.

The sooner the better..cause I am logging out soon.

Did you try this in K3 of the Class V Forecast by Month sheet?

=SUMIF('TY 11 Class V Justification'!$Q$16:$S$24,A3,'TY 11 Class V Justification'!$R$16:$T$24)

copied down... I seem to get correct results.. and I said, you can hide the 0's by custom formatting the columns to: 0;-0;;@

Well, I can see that you were summing values in one column based on matches to a specific input cell on another column, and I could see that you had a setup that is conducive to using SUMIF.

Normally, one would use SUMIF on single range input and output.. e.g.

=Sumif(A1:A10,X1,B1:B10)

but if you have a table setup such as yours is, where you have alternating lookup columns and amount columns, then you could expand Sumif to incorporate those, ie.

=Sumif(A1:C10,X1,B1:D10) ... noticing the offsetting of ranges to start at first lookup column and end at last lookup column and then for sum ranges, similarily start at first summing column and ending at last summing column.

Exactly.... don't let them goof up!