SUMIF Assistance...I think

Im lost and a little frustrated

I have two different worksheets

On sheet one I have a list of numbers in column E 1,3,4,6,78...

On sheet two I have a list of numbers in column C 1,2,3,4,5,6..... and a cooresponding set of values in column b

Im looking for a way to sum the cooresponding values into one cell for only the figures that match the numbers on the first sheet

If that doesnt make sence think of it like this
On sheet one I have a list of specific product numbers in column F(not in any specific order)
On sheet 2 I have a list of all products in column C and the quantity of each of those products I have sold in column B. I want to get a sum of only the products listed in sheet one sold in a single cell without modifying the data.

Originally Posted by freakadlibitur

Im lost and a little frustrated

I have two different worksheets

On sheet one I have a list of numbers in column E 1,3,4,6,78...

On sheet two I have a list of numbers in column C 1,2,3,4,5,6..... and a cooresponding set of values in column b

Im looking for a way to sum the cooresponding values into one cell for only the figures that match the numbers on the first sheet

If that doesnt make sence think of it like this
On sheet one I have a list of specific product numbers in column F(not in any specific order)
On sheet 2 I have a list of all products in column C and the quantity of each of those products I have sold in column B. I want to get a sum of only the products listed in sheet one sold in a single cell without modifying the data.

Hi,

Probably the easiest way is to put the the following formula in a cell on the same row as the first item on sheet1 and copy it down.

Please Login or Register  to view this content.

So suppose you put this in say G1:G100, in another cell have the formula:

Please Login or Register  to view this content.

Change the range 1:100 to suit.

There's probably a composite array formula that will do all that in one hit, but I can't think of it just at the moment. I'll come back if I do.

Rgds

Thanks for the info

The problem Im running into is that I only have one cell to work with otherwise Id run the formula 100 times and sum the results.

Originally Posted by freakadlibitur

Thanks for the info

The problem Im running into is that I only have one cell to work with otherwise Id run the formula 100 times and sum the results.

The suggestion I made did in fact give you the total in a single cell. My G column was only a helper column and could be placed anywhere in the workbook, tucked away out of sight if necessary, so I'm slightly puzzled therefore when you say you only have one cell to work with.

Rgds

I understand Richard. What I mean is that I only have one cell in the sheet that I'm able to modify. Yes, if I had free range on the worksheet then your suggestion would certainly be a good one and I thank you for it. With the limitations that have been placed on me I only have one cell to do all of this. Its silly I know.

Assuming your list of products in sheet1 is in E2:E50 then in sheet2 try this formula

=SUMPRODUCT(--ISNUMBER(MATCH(C2:C100,sheet2!E2:E50,0)),B2:B100)

Originally Posted by daddylonglegs

Assuming your list of products in sheet1 is in E2:E50 then in sheet2 try this formula

=SUMPRODUCT(--ISNUMBER(MATCH(C2:C100,sheet2!E2:E50,0)),B2:B100)

Man you just made my day! It worked thank you so much!