Thread: SUMIF based on monthly accumulation

In column A, I have each day of August in a separate row.
In column B, I have # of trans processed each day.
In column C, I need to asses a per trans fee of .45 if there are more than 100 trans for the month.

Without having to add an additional column to accumulate the number of trans for the month, is there a combination IF / SUMIF formula that i can give it to only assses the per trans fee if the monthly accumulation goes over 100? also -- on the specific row that it happens on, I only want to charge for the # of trans over 100, not necessarily the # of trans for that day.

Any help would be appreciated !!

What i'm trying to accomplish is the following:
IF
B5>100 AND
B5>B4 AND
B4<100
THEN (B5-100)*.40

AND

IF
B5>100
B5>B4
B4>100
THEN (B5-B4)*.40

ELSE

0

I set up a test sheet for this, with rows from 1 to 30, if your range is larger you'll have to adjust this formula, but this works:

=IF(SUMPRODUCT(--(MONTH($A$1:$A$30)=MONTH(A1)),$B$1:$B$30 )>100,B1*0.45,0)

The final zero in that formula is what is returned if there are 100 or fewer transactions in the month, because I wasn't sure what you wanted to return in that case.

Hey..you can use SUMIF(F15:F25,">0")/MAX(1,COUNT(F15:F25)) formula if u r getting any DIV/0! error message

Software as a Service | Sales Generator Systems