#### bucci35

##### Active Member
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Domenic

##### MrExcel MVP
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
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
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

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
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

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
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
...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!

Replies
5
Views
193
Replies
10
Views
240
Replies
5
Views
114
Replies
13
Views
473
Replies
1
Views
95

1,141,704
Messages
5,707,970
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.

### Which adblocker are you using?

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

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