MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using SUMIF for a table of values??


Posted by B Watson on May 04, 2001 8:30 AM

How do I use SUMIF when I want to sum the values that match a keyword (in column a) for a specific store location in row a. Table is 56 columns wide and about 25 rows deep.

store 1 2 5 7 16 27
men
men
women
women

Thanks...


Posted by Kevin James on May 04, 2001 8:41 AM

See incoming email

Posted by Aladin Akyurek on May 04, 2001 8:57 AM

Care to provide a 5 by 5 snippet of your data? You could activate an empty cell, type =, select an area of 5 by 5 of your data, and then hit CONTROL+SHIFT+ENTER at the same time. Go to the formula bar, select the formula and hit F9. Copy the array after = and paste it in the follow-up posting.

Aladin

Posted by Dave Hawley on May 04, 2001 2:08 PM


Hi B Watson

If your Store headings are in row 1 then you could use this:

=SUMIF(A1:B500,A3,INDIRECT(ADDRESS(1,MATCH("Store3",1:1))&":"&ADDRESS(500,MATCH("Store3",1:1))))

Where A3 contains the Value to look for in Column A.

This would sum the Column that had the heading "Store3" and had the same value (or text) as A3 on the corresponding row.

=SUMIF(A1:B8,A3,INDIRECT(ADDRESS(1,MATCH("Store3",1:1))&":"&ADDRESS(500,MATCH("Store3",1:1))))

Dave


OzGrid Business Applications

Posted by Dave Hawley on May 04, 2001 2:09 PM

Oops , ignore the second formula

Dave

OzGrid Business Applications