"Sumif" modification

**ALEZI** · 01-25-2012, 09:33 AM

Please guys help me, now I have this formula:

=Sumif($A$34:$J500,C4,$L$34:$L$500)

As you can see now when i execute my formula I would pick up in the column L all numbers when in column A was value from the cell C4, can you do modification and take from the column L only last 6 times when C4 would be matched in the column A.

Thank you a lot in advance.

**NBVC** · 01-25-2012, 09:44 AM

Try:

=SUM(IF($A$34:$J500=C4,IF(ROW($A$34:$J500)>=LARGE(IF($A$34:$J500=C4,ROW($A$34:$J500)-ROW($A$34)+1),6),$L$34:$L$500)))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

**ALEZI** · 01-26-2012, 07:22 AM

Thank you a lot,but for some reason this formula not worked on the my sheet.

I made one simple sample maybe could help you..

**OnErrorGoto0** · 01-26-2012, 07:26 AM

That is because you did not mention that your criterion could appear in one of several columns. In your example, could you have T in both columns on the same row?

**daddylonglegs** · 01-26-2012, 08:06 AM

I think NBVC's formula accounts for multiple rows........but the last ROW function shouldn't be there, so applied to your small sample formula should be

=SUM(IF($B$5:$D$13="T",IF(ROW($B$5:$D$13)>=LARGE(IF($B$5:$D$13="T",ROW($B$5:$D$13)),6),$E$5:$E$13)))

confirmed with CTRL+SHIFT+ENTER

Clearly your sample has only 6 "T"s but that formula should only count the last 6 rows if you have more. It might not work as you want if there can be multiple instances of "T" in a single row as 0EGO suggests, is that a possibility? What should happen in that case?

The above formula will also fail if there are fewer than 6 instances...is that possible?

**OnErrorGoto0** · 01-26-2012, 08:10 AM

Originally Posted by daddylonglegs

I think NBVC's formula accounts for multiple rows

You are of course correct. I admit that having noticed that the original SUMIF was flawed (I wonder what is in columns M-U), I assumed the multiple column range in the first argument was a typo and did not really look at the references in NBVC's formulas.

**daddylonglegs** · 01-26-2012, 09:55 AM

D'oh!

Of course I meant multiple columns.....but you knew that...

Originally Posted by OnErrorGoto0

I admit that having noticed that the original SUMIF was flawed (I wonder what is in columns M-U),

Yes, you're right. It's not clear if the intent is to sum those columns too - I suspect it isn't - text implies only summing L based on A

**OnErrorGoto0** · 01-26-2012, 10:02 AM

Indeed - that is what threw me with the example. It seems to confirm that the original SUMIF formula was not a typo, just an incorrect formula, since it will include columns M-U in the sum range, whereas the intent seems to be a multi-column criteria range but single column sum range.

**ALEZI** · 01-27-2012, 05:42 AM

Ok guys, first of all thank you for your effort to help me, yes you have a right “sample” and first post on the forum are different, here is only relevant the “sample” document .

In my document I have mere then six “T” in both column (B and D), there is not possible to happened multiple of “T” in same raw.

Main wish is to take all numbers in E column when “T” would be located in the column B or column D but not all than only last six times (there is no matter which columns contain D or B).

I hope that I now made a little more clear situation.