Transfer sums in one column based on date to another cell.

[avidcat]

Attached is an example of what I am trying to do.

I am looking to transfer the sums for a calendar month in one column to cells in another location for each month.

I put some notes in the sample attached, I appreicate any help getting this to work.

thanks,
Nick

[zbor]

Here.. Adjust formula for second part (July - December)...

See how I make dates from E2-E7Exception%20Sample%20(2)(1).xlsx

[avidcat]

Thanks, got it. Works perfect.

thanks,
Nick

[avidcat]

I am having a small problem, when I expanded the range to include future dates I am getting a #value error.

See the attached.

thanks,
Nick

[arthurbr]

Normal,

SUMPRODUCT wants the same ranges Try =SUMPRODUCT(--(MONTH($A$17:$A$500)=MONTH(E4)),$B$17:$B$500)

[avidcat]

Thanks, I have that working on my sample sheet that I posted, but when I tried to use it on my working project it doesn't work.

The reason is that on my other excel sheet I have hidden reference cells to gather the information in column A.

See attached.

thanks,
Nick

[zbor]

Well, you could change formula =IF(ROWS($A$7:$A7)>$D$25;0;INDEX('Accounting Exceptions'!$3:$3;MATCH(ROWS($A$7:$A7);'Accounting Exceptions'!$71:$71;0))) instead of "".

And then by conditional formating make white font if value is 0

[avidcat]

Cool idea, thanks.

[avidcat]

Is it possbile when using the formula:
=SUMPRODUCT(--(MONTH($A$10:$A$500)=MONTH(D4)),$B$10:$B$500)

If the Month from D4 is not found anywhere on A10 to A500 that instead of displaying 0 it displayed a blank cell?

Attached is an updated sample.

thanks for any help,
Nick

[avidcat]

Nevermind, that will not work either. Because if there is a month were they obtain no exceptions they need to also get a -1.

I was trying to figure out the best way to do this, I think there is too many variables.

[avidcat]

These are my variables:
--------
The exception policy will be on a rolling 6 month cycle. The exception will be removed from the employees file at the end of a calendar six (6) month period.

The Exception policy will be as follows:
Every five (5) exceptions in one calendar month=One point
One (1) or less exceptions in one month=One point removed

-------

I was close to designing this excel form to handle this, but I don't know how to get the results I need.

I appreciate all the help that everyone gave.