trouble adding multiple columns

[bucci35]

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!

 

[Domenic]

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]

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]

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]

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]

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]

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]

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]

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