trouble adding multiple columns

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
Hi,

I have a spreadsheet with 2000 rows of data with about 7 columns.
column A=store name
coumn B=Category name
column c=upc/description
column d through g has sales data with headers being weekending dates.

Each store can have the identical information.
I want to be able to grab, let's say, all upc/descriptions, for a specific weekending date total them up and have that figure show up in another sheet/cell. I've tried vlookups but can't seem to figure this out.
Please help!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
To sum the column for the specified weekending date, try...

=SUM(INDEX(D2:G2000,0,MATCH(J2,D1:G1,0)))

...where J2 contains the date of interest.

Hope this helps!
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
Let me clarify,

I am working off a different sheet and want to be able to use a formula that will look down the list to identify all the items that have a specific name,upc, description for each store, go across to a specific week ending date that I select then go down and add all these items up
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
It's unclear whether you'd like the store name or category name. Assuming that you want the store name and the UPC (I didn't see a separate column for Description), try the following formula...

=SUM(IF($A$2:$A$2000="StoreName",IF($C$2:$C$2000="UPC",INDEX($D$2:$G$2000,0,MATCH(J2,$D$1:$G$1,0)))))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319

ADVERTISEMENT

I keep getting a value error on this one. Also will this locate all items that have the specific upc's and only add them up?
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
This should say trouble adding multiple rows

I am trying to add multiple row by searching data base by store and upc. all stores will have same upc's. I want to find a particular upc for each store then add these up
a7:a10 have store names
b7:b10 have upc's
c6:h6 have weekdending dates
range of c7:h10 has data in dollars

I think last time I was unclear
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726

ADVERTISEMENT

Try...

=SUM(IF($A$7:$A$10="StoreName",IF($B$7:$B$10="UPC",INDEX($C$7:$H$10,0,MATCH(J2,$C$6:$H$6,0)))))

...where J2 is the weekending date of interest. If UPC is a numerical value, remove the quotes. Also, the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, type the formula, but instead of pressing just ENTER, press both CONTROL and SHIFT keys down, then while both keys are pressed down, press the ENTER key. Excel will automatically place braces {...} around the formula indicating that you've entered the formula correctly.

Hope this helps!
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
Wow,

Now it works perfectly, thank you very much.
Now, is thier a way I can count how many entries this formula found so that I can get an average of the sum?

Thanks again your a life saver!

Dan
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
...thank you very much.

You're very welcome!

Now, is thier a way I can count how many entries this formula found so that I can get an average of the sum?

Simply change SUM to AVERAGE. However, if a separate count is needed, the following formula can be used...

=SUMPRODUCT(--($A$7:$A$10="StoreName"),--($B$7:$B$10="UPC"))

Hope this helps!
 

Forum statistics

Threads
1,141,707
Messages
5,707,988
Members
421,539
Latest member
zuniBM

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