need help please for excel formula

[Apollo1979]

Hi Guys,
first of all I would like to thank you, I'm stuck with a formula which it goes way out of my knowledge.


A B C D
1 store1 1 2 1
2 store 2 0 2 1
3 store 4 1 2 4
4 store 1 5 0 0




In column A1 to A1000 I have stores name wich they could repeat more times.

I'm looking for a formula that tells me if in a column a1:a1000 has value store 1 what would it be the amount of products contained in b1 to d1000?

Example
store 1 = 9
Store 2 = 3
etc. etc.

I hope it all makes sense

Thanks Again
Ivan

 

[Firefly2012]

Hi Ivan

Please try:

=SUMPRODUCT((A1:A1000="Store1")*B1:D1000)

The "Store1" value may be replaced by a cell reference containing the store name

 

[Apollo1979]

Thank You Fyrefly,
I did try changing store1 to a cell reference but it give me value 0, when if I type store name it gives me 10

 

[Firefly2012]

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Store1	1	2	1			Store1	9
2	Store2	0	2	1			Store2	3
3	Store4	1	2	4			Store4	7
4	Store1	5	0	0
5
Sheet1

Cell Formulas
Range	Formula
H1	=SUMPRODUCT(($A$1:$A$4=G1)*$B$1:$D$4)
H2	=SUMPRODUCT(($A$1:$A$4=G2)*$B$1:$D$4)
H3	=SUMPRODUCT(($A$1:$A$4=G3)*$B$1:$D$4)

 

[Apollo1979]

feel like a donkey right now,
so here the formula I'm using

=SUMPRODUCT(($D$8:$D$1000=P8)*$G$8:$I$10000)
and with this I get #N/A...

 

[Firefly2012]

To get that error, you must either not have the value of P8 in D8:D1000 or you already have a #N/A in D8:D1000 or G8:I1000

By the way, your ranges in your quoted formula are different sizes (the purple one is to row 10,000). This wouldn't cause a #N/A though

 

[Apollo1979]

AWESOMEEEEEEEEEE....YOU'RE THE BEST!!!!!
It works out amazingly!!!!!!!!

I LOVE YOU FIRE FLY

 

[Firefly2012]

You're welcome