Sumproduct and Countif together

[lindsey1975]

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

 

[Pete_UK]

Try this in B1 of Sheet2:

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

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

Hope this helps.

Pete

 

[RagDyeR]

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(Sheet1!$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!
=====================================================


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

 

[Pete_UK]

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