Sumproduct and Countif together
 

Hi

I need to create a formula for the following: - on one sheet i Have a
group of part number

1 123456
6 123456
7 123456
1 456789
3 789123
4 789123

On another sheet I have a table the part numbers, wanting the gross
qty sold over then month

123456 ?
456789 ?
789123 ?

and i need to be about to automatically add them all up at once, and
also (to top it off) leave the cells blank of the parts numbers not
sold.

Can anyone help, my head is spinning :-)

Regards

Linexe

Sumproduct and Countif together
 

Try this in B1 of Sheet2:

=IF(COUNTIF(Sheet1!B1:B100,A1)=0,"",SUMPRODUCT((Sh eet1!
B1:B100=A1)*(Sheet1!A1:A100)))

Assumes you have 100 items on Sheet1 - adjust to suit.

Hope this helps.

Pete

On Apr 2, 2:56 pm, wrote:
Hi

I need to create a formula for the following: - on one sheet i Have a
group of part number

1 123456
6 123456
7 123456
1 456789
3 789123
4 789123

On another sheet I have a table the part numbers, wanting the gross
qty sold over then month

123456 ?
456789 ?
789123 ?

and i need to be about to automatically add them all up at once, and
also (to top it off) leave the cells blank of the parts numbers not
sold.

Can anyone help, my head is spinning :-)

Regards

Linexe

Sumproduct and Countif together
 

Say your sales quantity is in Column A of Sheet1, from A1 to A20,and the
part numbers are in Column B, from B1 to B20.

Your datalist of original part numbers are on Sheet2, in Column A.

Enter this formula in B1 of Sheet2:

=IF(ISNA(MATCH(A1,Sheet1!B1:B20,0)),"",SUMIF(Sheet 1!$B$1:$B$20,A1,Sheet1!$A$1:$A$20))

Copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


wrote in message
ups.com...
Hi

I need to create a formula for the following: - on one sheet i Have a
group of part number

1 123456
6 123456
7 123456
1 456789
3 789123
4 789123

On another sheet I have a table the part numbers, wanting the gross
qty sold over then month

123456 ?
456789 ?
789123 ?

and i need to be about to automatically add them all up at once, and
also (to top it off) leave the cells blank of the parts numbers not
sold.

Can anyone help, my head is spinning :-)

Regards

Linexe

Sumproduct and Countif together
 

You appear to have put quotes within the two sumproduct brackets.
Also, the
ranges have to be the same size - you have one from 5 to 50 and the
other
from 5 to 49. Try this instead:

=IF(COUNTIF(Apr!E5:E50,B5)=0,"",SUMPRODUCT((Apr!E5 :E50=B5)*(Apr!
G5:G50)))

All one formula - watch out for line-wrap.

By the way, I couldn't reply to your email address - it was bounced
back.

Hope this helps.

Pete


Hi Pete

This is what i have come up with, can you see where I might be going
wrong


=IF(COUNTIF(Apr!E5:E50,B5)=0,"",SUMPRODUCT(("Apr!E 5:E50=B5)*(Apr!
G5:G49")))

Thanks

Lindset



On Apr 2, 3:27 pm, "Pete_UK" wrote:
Try this in B1 of Sheet2:

=IF(COUNTIF(Sheet1!B1:B100,A1)=0,"",SUMPRODUCT((Sh eet1!
B1:B100=A1)*(Sheet1!A1:A100)))

Assumes you have 100 items on Sheet1 - adjust to suit.

Hope this helps.

Pete

On Apr 2, 2:56 pm, wrote:



Hi

I need to create a formula for the following: - on one sheet i Have a
group of part number

1 123456
6 123456
7 123456
1 456789
3 789123
4 789123

On another sheet I have a table the part numbers, wanting the gross
qty sold over then month

123456 ?
456789 ?
789123 ?

and i need to be about to automatically add them all up at once, and
also (to top it off) leave the cells blank of the parts numbers not
sold.

Can anyone help, my head is spinning :-)

Regards

Linexe- Hide quoted text -

- Show quoted text -