How to use SUMIF on consecutive cells matching criteria

**panamakevin** · 05-28-2009, 04:13 PM

How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)

=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")

More data:
Column A is a group of dates, but not in order, they are slightly mixed up
Column H is a monetary value.

Thanks in advance

**NBVC** · 05-28-2009, 04:29 PM

Maybe better to post an actual workbook example showing some expected results...

**panamakevin** · 05-28-2009, 04:36 PM

Here is an Example, I put notes in column K

**mewingkitty** · 05-28-2009, 04:37 PM

Eh, kinda hard to say without being able to see the sheet itself... but could you use a helper column to determine which cells meet the consecutive criteria, then sumif that helper column?

**NBVC** · 05-28-2009, 04:43 PM

Does this in I5, copied down work?

=IF(A6<>A5,IF(A4<>A5,H5,SUMIF(A$5:A5,A5,H$5:H5)),"")

**NBVC** · 05-28-2009, 04:54 PM

Actually, probably, this one is more accurate:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down..

**panamakevin** · 05-28-2009, 05:07 PM

Well it works for the groups but unfortunately its no longer giving me the 'interest by date' for the single, non-grouped dates. Looks like I get to learn a few new commands today (MATCH, INDEX) heh heh. Thanks as always NBVC

**NBVC** · 05-28-2009, 09:04 PM

I am not sure what you mean.. see attached I put formula in column J..

It seems to me they are summing in groups and/or individuals.

**panamakevin** · 05-29-2009, 01:38 PM

Ok, So I got it working last night. I kept getting a reference error when i plugged it into column I so instead I threw it into column J like you had done and I just had I reference J then hid columns J & K. Thanks for the help again NBVC!!! I am getting a stronger and stronger grasp of this stuff. I just started learning VB last night, so I look forward to many more conversations regarding functions and programming...