conditional sum based on range headers

  • Thread starter Thread starter Alexa
  • Start date Start date
A

Alexa

How can I get the sum of all cells in a range which have
a certain column and row header? I tried looking at the
SUMPRODUCT function, but I can't figure out how to tell
the function to look at the column and row headers. If
I'm not making sense just ask and I'll try to be more
clear. Thanks for your help.

More specifically I have a table of prices, dates on the
y axis, and categories on the x axis. What I want to do
is apply a sum to the whole table that will add up each
month's total for each category. For example to get the
Jan total for Supplies I would call summonth(1, Supplies,
B2:F20) where column A has dates, and row 1 has
categories.
 
Hi Alexa
you may try the following
=SUMPRODUCT(--(MONTH(A2:A100),OFFSET(A2:A100,0,MATCH("Supplies",B1:F1,0
)))
 
Thanks frank, that almost works. It takes the month for
every row (not only certain month rows) and multiplies by
the value, when it should only be adding the cells which
have jan as the row header.

Here is the exact formula I'm using.
=SUMPRODUCT(--(MONTH(A2:A7)), OFFSET(A2:A7,0,MATCH
("bells",B1:C1,0)))

Maybe the problem is I filled in the missing paren in the
wrong spot? Your original formula was unbalanced.

There needs to be some kind of match against the month
column, I'm trying to figure it out based on the current
formula you gave me but am having no luck.
 
=SUMPRODUCT(--(TEXT(DateRange,"mmm-yy")=TEXT(X1,"mmm-yy"),INDEX(TableRange,0
,MATCH(X2,CategoryRange,0)))

where Table refers to the range without the row housing categories,
DateRange to the range that houses the dates, X1 to a date in the form of
1/1/04 (m/d/yy) to indicate the month/year of interest, X2 to a category of
interest.

You can also build a pivot table using grouping by month.
 
Hi
no I just forgot the condition. Try
=SUMPRODUCT(--(MONTH(A2:A100)=1),OFFSET(A2:A100,0,MATCH("Supplies",B1:F
1,0)))
 
Hi
the -- converts the boolean value returned by the comparison to 0/1
(FALSE = 0; TRUE = 1)
It is a double minus
 
Back
Top