+ Reply to Thread
Results 1 to 3 of 3

SUMIF with contingency on either font color or date range

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    Shelton, Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    6

    SUMIF with contingency on either font color or date range

    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

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,942

    Re: SUMIF with contingency on either font color or date range

    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.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-19-2013
    Location
    Shelton, Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: SUMIF with contingency on either font color or date range

    Thank you! That's perfect because I only have the one month of checks per worksheet. You're a life saver!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1