SUMIF with contingency on either font color or date range

**hbgrl1365** · 03-19-2013, 09:17 AM

I am by no means experienced in excel but I am usually pretty good and just messing around and figuring out how the formulas work but maybe I am asking for too much this time.

I have a list of checks and their amounts. Check amount is in column G and the date the check cleared our bank is in column A. I am trying to sum the checks that cleared the bank in the following month. I tried =SUMIF(A1:A100, "2*", G1:G100) to yield a sum of January's checks that cleared the bank in February (I hope that makes sense). I also tried putting the 2* in a seperate cell and replacing it in the formula with the cell number. As a note: I color code the date cleared column as checks cleared in the current month in green font and checks cleared in the following month in red font and added a module to use a formula "=CountColor" to yield a total of how many checks cleared the bank in current or in transit based on the color of my text. So if I could use the color of the text in column A as a contingency for the formula to include the amount in column G in the final sum that would work too. I feel like I made this way more complicated that it needs to be but it really will make my life much easier. Am I just trying to do something that can't be done?

Thank you in advance!
Amanda

**ChemistB** · 03-19-2013, 09:32 AM

Two options
You could use SUMIFS like so
=SUMIFS(G1:G72,A1:A72,">=2/1/2013", A1:A72, "<3/1/2013")

or SUMPRODUCT like so
=SUMPRODUCT(G1:G72,--(MONTH(A1:A72)=2))
(note, if you have multiple years, that sumproduct will pull all February checks. You could add another argument for year)
Hope that helps.