+ Reply to Thread
Results 1 to 16 of 16

COUNTIF formula where part of the range reference needs to use the result of a formula

  1. #1
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    COUNTIF formula where part of the range reference needs to use the result of a formula

    Hello there!

    I have a workbook with 4 spreadsheets; only the first 2 are relevant right now. The first one is called "MONTH END TOTALS" and the second is called "COMPLETE2".
    • COMPLETE2 has a LOT of rows (thousands), with column A being dates. The number of rows on this sheet will vary and there might (rarely) be blank rows scattered in there.
    • MONTH END TOTALS has a series of tables to summarize the data from COMPLETE2. The top row of each table lists the months of the year and just above that is the year.


    My first problem was finding out where the last row of data is on COMPLETE2 just using a formula. (Initially, my coworker asked for a macro that would carry out the work on this workbook, but I thought that if I could get formulas to do what's needed, the sheet would always be up-to-date.) I planned to incorporate that formula into the others that will populate the rest of the table on MONTH END TOTALS, but it was going to make for some RIDICULOUSLY long formulas. This is where the question in my subject is aimed: So, I put a formula in O20 on MONTH END TOTALS that will keep track of what row is the last one on COMPLETE2 with data. Then I thought it'd be easy to use O20 in the other formulas as part of the range reference. (For example - A2:A (O2)). I was WRONG!! Very, VERY wrong! I've tried innumerable formulas and syntaxes and I just can't get anything to work. Two of my attempts are shown on MONTH END TOTALS, cells C3 and K3.

    In the first data row of the first table I need to have a formula that tells me how many rows on COMPLETE2 have a date where the month/year corresponds to the month/year for that cell in the table.
    So, on the attached workbook, MONTH END TOTALS cell C4 should show how many rows on COMPLETE2 are for AUG 2017, for example. That would be 7 rows. Cell K4 should show how many rows on COMPLETE2 are for APR 2017.

    I hope I have explained my problem(s) at least a little clearly; I really tried to make sense. Please let me know if I've been totally confusing. I've been wrestling with this for 2 solid days now and my brain is DEAD!

    Thanks for any help!

    Jenny
    Attached Files Attached Files
    Last edited by zookeepertx; 10-31-2018 at 05:55 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    This will return the last used row in column A in the sheet Complete2

    =MATCH(99999999999,COMPLETE2!A:A)

    You need to use the indirect function to build a dynamic range into a formula

    Try this formula in C4. Enter it using Ctrl Shift Enter. It returns 7.

    =SUM(IF(UPPER(TEXT("01/" & MONTH(COMPLETE2!A2:A20) & "/" &C2,"MMM"))=C3,1,0))

    This uses the indirect Function and also returns 7


    Enter into C4 using Ctrl Shift Enter as it is an array formula.
    =SUM(IF(UPPER(TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) & "/" &C2,"MMM"))=C3,1,0))


    This also returns 7 but is entered using Enter only. So it is not an array formula.
    Last edited by mehmetcik; 10-31-2018 at 06:46 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    Maybe a tad shorter:

    =MATCH(99^99,COMPLETE2!A:A)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    Thanks for your quick reply!

    I did finally get that figured out; that's what I put in O20 on MONTH END TOTALS to keep track of what the last row with data is on COMPLETE2. I can't seem to discover how to use the value (currently 20) in that cell as part of the range reference in the other formulas.

  5. #5
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    Quote Originally Posted by AliGW View Post
    Maybe a tad shorter:

    =MATCH(99^99,COMPLETE2!A:A)
    Interesting! I'm leaving for the day now, but have a try at that tomorrow!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    To return the value of the last populated cell in the range:

    =INDEX(COMPLETE2!A:A,MATCH(99^99,COMPLETE2!A:A))

  7. #7
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    I've got O20 showing the result of that shortened formula (Thanks for that! Much neater!), but I need to use the value shown in that cell in another formula

    My main problem (currently) is that, if I use this - COMPLETE2!A:A - it looks at ALL of column A and it takes FOREVER. I want to limit the range to COMPLETE2!A$2:A$ (whatever the last data row is). Cell O20 on MONTH END TOTALS gives me that last row number, so I need the other formulas to reference $A$2:$A$(value from O2). But it just refuses to do that, no matter how many different syntaxes I've tried, I just keep getting error messages! It's REALLY frustrating and, since this is my 3rd day thinking about this, I'm about to tear my hair out! I've tried using SUMPRODUCT (probably incorrectly) and INDIRECT (probably incorrectly, too), COUNT and COUNTIF, but I'm getting nowhere.

  8. #8
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    I've got this in MONTH END TOTALS, cell C4: =COUNTIF((MONTH(COMPLETE2!$A$2:$A$ & CELL("contents",$O$2)),MONTH(--(C3&" 1, 2017"))&"/"&$C2)) - but it objects to $A$, if that helps at all.

  9. #9
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    Entered into C4 using Ctrl Shift Enter

    =SUM(IF(UPPER(TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) & "/" &C2,"MMM"))=C3,1,0))
    Last edited by mehmetcik; 11-01-2018 at 02:42 PM.

  10. #10
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    I'm sorry, mehmetcik; I meant to say that I also tried the 2 formulas you posted up above and it just gave me 0. I just tried the most recent version and IT results in 0 as well.

  11. #11
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    It just seems like no matter how I try to refer to the O2 - or the value in it - it fails at $A$!
    $A$2:$A$ & O2
    $A$2:$A$ & (O2)
    "$A$2:$A$" & O2
    $A$2:$A$ & VALUE(O2)
    $A$2:$A$ & ("O2")

    and many other variations; they all fail! A few things have failed with the entire formula highlighted, but mostly its that bit there.

    Jenny
    Last edited by zookeepertx; 11-01-2018 at 03:49 PM.

  12. #12
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    Workbook attached.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    Hi mehmetcik,

    I got covered up in work and just got back to this. This stupid problem is driving me CRAZY!

    I downloaded your workbook and the formula in C4 actually DID show 7, like it should. I wanted to see how it worked so that I could continue with it, so I went to Evaluate Formula but, when it finished, it left 0 in C4! So, I figured that maybe an array formula can't be Evaluated and went up in the formula bar, entered the formula with Ctrl Shift Enter but it still returns 0 now! I BROKE it!

    I didn't save the changes on your workbook and closed it, then reopened it again and now it still doesn't work like it did when I first opened it.

    I've been poking around at it for an embarrassingly long time now and can't get it to operate correctly again, except for 1 instance. I moved the formula over to "K", which has APR in row 3 and I changed the reference to "K", entered with Ctrl Shift Enter and it returned the correct number! But then I broke it again and now I'm stuck. Again!

    One thing I did notice as I was going through Evaluate Formula was that this part: TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:!" & O2)) created a range of dates all starting with 01/ (which it then translated to JAN for the MMM). Also, when use count or sum or whatever and refer to the data on COMPLETE2! don't I need to use $ before the characters denoting the range to keep the formula from trying to look at the ENTIRE column?

    I'm so frustrated and feeling like a complete and utter failure. (This doesn't SEEM like it should be this difficult! I don't get it)

    Thank you!

    Jenny
    Last edited by zookeepertx; 11-08-2018 at 12:32 PM.

  14. #14
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    It still works for me.

    paste this formula in C4.

    =SUM(IF(UPPER(TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) & "/" &C2,"MMM"))=C3,1,0))

    Select C4

    Using your left hand, press and hold ctrl and shift

    then

    Using your right hand press enter.

    That is what we mean when we ask you to use ctrl shift enter.

    To see how the formula works select a discrete part of the formula and press f9.

    Eg. select INDIRECT("COMPLETE2!A2:A" &O2) and press f9

    You should see this series of numbers highlighted

    {42948;42948;42828;42948;42950;42828;42828;42948;42828;42828;42889;42828;0;42948;0;0;0;42948;42828}

    Those are dates displayed as numbers.

    Press esc to go back to your formula.

    Select MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) and press f9


    You should see this series of numbers highlighted

    {8;8;4;8;8;4;4;8;4;4;6;4;1;8;1;1;1;8;4}

    These are your months displayed as numbers, unfortunately no good to you as the Month in C3 is text ie AUG

    Press esc to go back to your formula

    Select TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) & "/" &C2,"MMM") and Press f9

    You should see this text series

    {"Aug";"Aug";"Apr";"Aug";"Aug";"Apr";"Apr";"Aug";"Apr";"Apr";"Jun";"Apr";"Jan";"Aug";"Jan";"Jan";"Jan";"Aug";"Apr"}

    These are your months displayed as Text, unfortunately no good to you as the Month in C3 is upper case text ie AUG

    Press esc to go back to your formula

    Select UPPER(TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) & "/" &C2,"MMM")) and press f9

    You should see this text series

    {"AUG";"AUG";"APR";"AUG";"AUG";"APR";"APR";"AUG";"APR";"APR";"JUN";"APR";"JAN";"AUG";"JAN";"JAN";"JAN";"AUG";"APR"}

    Now we are talking. We can compare those to C3 and get useful results

    Select IF(UPPER(TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) & "/" &C2,"MMM"))=C3,1,0) and press f9

    You should see this series of number

    {1;1;0;1;1;0;0;1;0;0;0;0;0;1;0;0;0;1;0}

    The ones tell you which entries are in the month selected in C3. The sum function simply adds them up to give you 7.

  15. #15
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    No, I know how to apply Ctrl Shift Enter, it just doesn't want to work for me this time!
    However, I LOVE the F9 feature! How come nobody told me about that years ago, LOL?! VERY HANDY!

    Quote Originally Posted by mehmetcik View Post

    Select MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) and press f9
    You should see this series of numbers highlighted
    {8;8;4;8;8;4;4;8;4;4;6;4;1;8;1;1;1;8;4}
    These are your months displayed as numbers, unfortunately no good to you as the Month in C3 is text ie AUG
    Everything is good up to this point.

    Press esc to go back to your formula
    Select TEXT("01/" & MONTH(INDIRECT("COMPLETE2!A2:A" &O2)) & "/" &C2,"MMM") and Press f9
    You should see this text series
    {"Aug";"Aug";"Apr";"Aug";"Aug";"Apr";"Apr";"Aug";"Apr";"Apr";"Jun";"Apr";"Jan";"Aug";"Jan";"Jan";"Jan";"Aug";"Apr"}
    These are your months displayed as Text, unfortunately no good to you as the Month in C3 is upper case text ie AUG
    This is where the problem occurs. Mine says {"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan";"Jan"}

    OOH, OOH, OOH! I just figured it out!!I just hd to change it and put the "01/" after the numbers referring to the months! (United States and all, LOL!)

    YIPPEEE! I'm so happy! Great way to end the day!
    Thank you SO much; you made my day!

    Jenny
    Last edited by zookeepertx; 11-08-2018 at 07:19 PM.

  16. #16
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Re: COUNTIF formula where part of the range reference needs to use the result of a formula

    Hello again,

    I found 1 or 2 strange things going on (with the spreadsheet, not your formula) that I've been fussing with, but I think I have (most of) them fixed. I have to leave for the weekend in a few minutes, but will come back on Monday and let you know the outcome.

    Thank you and have a great weekend!

    Jenny

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] countif or sumproduct with 2 part formula.
    By jnepsa in forum Excel Formulas & Functions
    Replies: 28
    Last Post: 02-22-2017, 03:05 PM
  2. Reference a dynmaic range from one cell into a countif formula
    By jdoerr1021 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2017, 07:46 PM
  3. [SOLVED] Can I reference part of a formula
    By scudo in forum Excel General
    Replies: 3
    Last Post: 06-02-2015, 05:45 PM
  4. Replies: 7
    Last Post: 02-03-2013, 06:25 PM
  5. Using Range Name reference as part of R1C1 Formula
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-14-2012, 01:32 PM
  6. Display part of an array formula result
    By brookhyserj in forum Excel General
    Replies: 1
    Last Post: 02-07-2011, 12:57 AM
  7. [SOLVED] Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 PM

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