Trying to SUM INDEX but its not MATCHing

Question

Trying to match Note column with sum but currently, it's giving me the monthly total.

Current formula: =SUM(INDEX(C4:C47,0,MATCH($A$83,D4:D47,0)))

Sum should be 36.96 (18.99 + (5.99 x 3)).

@cybernetic.nomad Good catch. There's one more 5.99 not in the screenshot. — JMP, Apr 18 '19 at 18:15

score 2 · Answer 1 · answered Apr 18 '19 at 18:26

2

SUMIF is not an array formula:

=SUMIF($D$4:$D$47,A83,$C$4:$C$47)

answered Apr 18 '19 at 18:26

Scott Craner

score 1 · Accepted Answer · answered Apr 18 '19 at 18:23

1

Try this formula -

=SUMPRODUCT(($D$4:$D$47 = A83)*$C$4:$C$47)

answered Apr 18 '19 at 18:23

Imtiaz Ahmed

this is not the purpose of `SUMPRODUCT`, instead `SUMIF` it's been made for this – EttoreP Apr 18 '19 at 18:35
`SUMIF` is obviously good.. no doubt.. but `SUMPRODUCT` is also powerful. I use it more than `SUMIF` in my daily routine. – Imtiaz Ahmed Apr 18 '19 at 18:43
There are two arguments for SUMIF over SUMPRODUCT: 1. Quicker, it is not as overhead intensive. 2.Less typing. Where SUMIF does not work then I always fall back to SUMPRODUCT. But where it does work, it is saves time in those two ways. Your formula is not bad, but if the user had 10,000 rows, it would be slower than SUMIF. – Scott Craner Apr 18 '19 at 19:27
Yes, `SUMIF` and `SUMIFS` are significantly faster than `SUMPRODUCT`. They are really good. Your answer is perfect. But `SUMPRODUCT` is also not bad. You can see it here https://stackoverflow.com/a/27536083. It's more flexible. – Imtiaz Ahmed Apr 18 '19 at 19:34

2 Answers2