• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT based on column header criteria

post67

New Member
Hi, I have the following table that I like to use the SUMPRODUCT function,

but I can't seem to get it work


USER Jan Apr Mar May Feb Feb

A 565 76 6 876 54 54

B 65233 5 64 67 35 35

C 323 5555 5 54 1 1

D 221 6 8908 4 32 32

E 3 879 9 87 25 25

F 56 85 89 7 566 566


For user A, I want to sum the total for the month of Feb (because some months

could appear twice), so I want my answer to be (54+54) = 108. The table

above is from a pivot table and the columns could shift, so I want to make it

dynamic enough to sum it up if when the column moves.


Here's the formula I'm using

=SUMPRODUCT((A3:A9="A"),--(A3:G3="Feb"),A3:G9)

and I'm getting a #VALUE error.


Please help, thanks
 
Post67

give this a whirl:

=SUMPRODUCT(($B$3:$G$3="Feb")*OFFSET($B$3:$G$3,MATCH("A",$A$4:$A$9,0),))


But you probably want to link the inputs Feb and A to cells like

=SUMPRODUCT(($B$3:$G$3=$B$1)*OFFSET($B$3:$G$3,MATCH($B$2,$A$4:$A$9,0),))

where B1 will have Feb

and B2 = A
 
Back
Top