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
