I am using an array formula to match 2 values against a range. I would like to know if it is possible to have the formula sum every instance of the match.
Formula i use to match:
{=INDEX($G$1:$G$9,MATCH(A1&B1,$E$1:$E$9&$F$1:$F$9,0))}
I want to sum the values found in "$G$1:$G$9" every time (A1&B1) is found in "$E$1:$E$9&$F$1:$F$9"
=sumifs(g1:g19,e1:e19,b1,f1:f19,c1)
mind you id scrap the array and use
=INDEX($G$1:$G$9,MATCH(B1&C1,INDEX($E$1:$E$9&$F$1:$F$9,0),0)) but thats just my preference
you could also use sumproduct for backwards compatibility with earlier versions of excel
=SUMPRODUCT((E1:E9=B1)*(F1:F9=C1),G1:G9)
Last edited by martindwilson; 06-09-2012 at 07:18 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks