SUM COLUMN C WHEN CONDITIONAL FORMATTING IS APPLIED TO COLUMN A

[ugcs93]

Following a previous post from Hui, I was able to add a module and get the formula below to work. But how can I tweak it if the formating has been applied to the date column (e.g. column A) but I need to sum the cash column (e.g. column C)?

=ColorFunction($K$22,$M$1:$M$20,TRUE)

 

[Luke M]

Rather than trying to detect color/formatting, it would be easier to build a SUMIF (or SUMPRODUCT) based on the same rules that the CF is working on. Could you explain the logic showing which cells in col A are getting colored?

 

[ugcs93]

The dates are being colored by fiscal year (there's four years worth of records). The original idea was to filter by color and sum column c to get a total for the particular fiscal year.

 

[Luke M]

Assuming you know the start/end of a fiscal year, could you do something like this?

=SUMIF(Data,">="&StartDate)-SUMIF(Data,">"&EndDate)


If you provide us more info about the dates (how they're determined) we may be able to create a formula structure to help you automatically figure out StartDate and EndDate.

 

[ugcs93]

The fiscal year runs from August 1st thru July 31, beginning in 2007. I have a list of 6,000 records of gifts and I group these by fiscal year to get a total for the fiscal year. I do this on a monthly basis. So, I'm basically summarising 6,000 records. As I said, I had been filtering on color. I could probably use a pivot, but the spread is already huge with a number of weird and wonderful formulas for other calculations

 

[Luke M]

Assuming you have a year listed in some cell (A2), you could do:

=SUMIF(DateRange,">="&DATE(A2,8,1),SumRange)-SUMIF(DateRange,">"&DATE(A2+1,7,31),SumRange)


Assuming you have the list of years somewhere, you could then copy this formula down/right as needed.

 

[ugcs93]

That worked! Thank you for your time and genius

 

[Luke M]

Hurrah! Glad we finally got it figured out. =)