need help please for excel formula

Apollo1979

New Member
Joined
May 9, 2013
Messages
16
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

New Member
Joined
May 9, 2013
Messages
16
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Store1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Store1</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Store2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Store2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Store4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Store4</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Store1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$A$1:$A$4=G1</font>)*$B$1:$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$A$1:$A$4=G2</font>)*$B$1:$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$A$1:$A$4=G3</font>)*$B$1:$D$4</font>)</td></tr></tbody></table></td></tr></table><br />
 

Apollo1979

New Member
Joined
May 9, 2013
Messages
16

ADVERTISEMENT

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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

New Member
Joined
May 9, 2013
Messages
16
AWESOMEEEEEEEEEE....YOU'RE THE BEST!!!!!
It works out amazingly!!!!!!!!

I LOVE YOU FIRE FLY
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top