Sumif where various conditions need to be met

[SEJ50]

I would like to identify how to use one formula in spreadsheet 3 that adds the values from Spreadsheet 1 according to all the codes that fall within a specific category (listed in spreadsheet 2)
For example to find the total for the category Dogs there are 2 codes 2010 and 7480 and then both of these codes are in spreadsheet 1 with values of £20 and £1050.98 totaling £1070.98.

Spreadsheet1

Col A (list of 4 digit codes) Col B (Amount)
2010 20.00
4329 50.43
7480 1050.98


Spreadsheet 2

Col A (list of 4 digit codes) Col B (Category Heading)
2010 Dogs
4329 Cats
7480 Dogs

Spreadsheet 3

Col A (Category Heading) Col B
Dogs
Cats
Hamsters

I do hope that this is possible and that more importantly someone understands my explanation.

Many thanks

[Special-K]

in Sheet3!B1
=SUMPRODUCT((Sheet2!B$1:B$3=A1)*(Sheet1!A$1:A$3=Sheet2!A$1:A$3)*(Sheet1!B$1:B$3))

[SEJ50]

Thank you unfortunately I am getting a #value error

Do the columns with codes need to "numbers" or are they ok formatted as text.

In spreadsheet 1 there are some blanks cells in the list of codes does this matter?

other than that I am struggling to find anything that could be causing the problem.

Any suggestions?

Many thanks

[Special-K]

Blanks may cause a problem if thery're in the middle of numeric data.
SUMPRODUCT doesn't like text in the middle of numbers, all data should be numbers.

I thought it was gonna go this way.
It didn't look like your example data was a proper representation of your actual data so i thought there might be some hiccup.

Based on your example data:

[Special-K]

Can you post the file? Desensitise if it necessary.

[samba_ravi]

I think the below will work

=SUMIFS(Sheet1!$B$1:$B$3,Sheet1!$A$1:$A$3,INDEX(Sheet2!$A$1:$A$3,MATCH($A1,Sheet2!$B$1:$B$3,0)))