Sumifs not working

[cloughie]

Hi, I'm trying to write a sumifs formula but I'm having no joy. Hopefully someone out there can help.

I need to sum all the quantities that match the product and the method from sheet 1 into sheet 2

Sheet 1
Product Qty Method
ABC 7 Phone
DEF 4 Website
GHI 2 Phone
ABC 3 Website

Sheet 2
Product Phone Website
ABC 7 3
DEF 4
GHI 2

 

[steve the fish]

Like this:

=SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,$A2,Sheet1!$C:$C,B$1)

 

[cloughie]

That doesn't seem to work, it returns a value of 0 for every product any other suggestions?

 

[steve the fish]

That will work as long as the ranges have been input correctly and 'ABC' in one cell is definately equal to 'ABC' in another. Often there are leading or trailing spaces which cells that appear the same arent the same. Also check your numbers are numbers and not text.

 

[cloughie]

That will work as long as the ranges have been input correctly and 'ABC' in one cell is definately equal to 'ABC' in another. Often there are leading or trailing spaces which cells that appear the same arent the same. Also check your numbers are numbers and not text.

Thanks for your help on this I got this working but then encountered another issue..........I need some of the codes to include more than the initial one so that ABC and AVBC return a combined total within ABC on Sheet 2.

Sheet 1
Product Qty Method
ABC 7 Phone
DEF 4 Website
GHI 2 Phone
ABC 3 Website
AVBC 3 Website

Sheet 2
Product Phone Website
ABC 7 6
DEF 4
GHI 2

 

[steve the fish]

Is there any logic behind ABC being the same as AVBC?

 

[cloughie]

Is there any logic behind ABC being the same as AVBC?

We have some products under an old SKU but any sales under this code need to be added to the total of the new SKU

 

[steve the fish]

Then what i would do is convert any old skus into the new sku and do the sum on the new sku. You would get both then.