+ Reply to Thread
Results 1 to 9 of 9

Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    I have an existing workbook using COUNTIFS formulas that are working without issue. I want to share this workbook with a friend who only has Excel 2003 which only supports the COUNTIF formula. I am struggling to create an equivalent query for his workbook.

    Here is the formula that works in my workbook: = IF($C11="","",COUNTIFS(FT8_Log!$H:$H,"<"&K$6,FT8_Log!$H:$H,">"&J$6,FT8_Log!$R:$R,"="&$B11)) [The result is "1"]

    Here is where I am at with his workbook: = IF($C11="","",(AND((COUNTIF(FT8_Log!$H:$H,"<"&K$6)-COUNTIF(FT8_Log!$H:$H,">="&J$6)),COUNTIF(FT8_Log!$R:$R,"="&$B$11)))) [The result is "TRUE"]

    My formula generates a correct count based on two conditions; one is if a specific value is found within a range ($R:$R) and second is if a number value falls within a range of numbers ($H:$H).

    His formula only generates a "TRUE" or "FALSE" result, not a count.

    Source column $R:$R is alphanumeric.
    Source column "$H:$H is numeric only.

    I have spent the afternoon trying to create an equivalency and could use some guideance to a solution.

    Thank you in advance!
    Last edited by Robert Chudek; 10-05-2023 at 04:36 PM. Reason: To add "Solved" to the title

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    I don't think it works with COUNTIF.

    With SUM it doesn't seem to be a problem.

    Please try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,698

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    May be this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    05-28-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    I thought I had posted replies to the suggestions I received but I do not see them appearing in this thread. I have had very intermittent internet connectivity the last few days so my replies may have disappeared into the "bit bucket" somewhere along the line.

    So far, none of the suggestions have worked when using Excel 2003. I am trying different approaches but I have not come to a solution yet. In the older Excel, the roadblock appears to be counting the number of occurrences when there are more than two conditions that need to be met. There is also the syntax issue of counting the mixture of a string and a number.

    I could use some more recommendations if you have any. Stating the problem verbally, here is what I need to count:

    IF a text string "ZZZ" is found within "Column "A", AND the number "123" in "Column B" is >100, AND that same number "123" in "Column B" is <200, give me the count of how many times those three conditions are met and place the resulting count in "Column C."

  5. #5
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    I have looked into the history books of Excel and in Excel 2003 full column references are not allowed. Which lead to #NUM.
    For that reason, the formulas from Post #2 and #3 do not work.

    So try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If necessary, confirm the formula with Ctrl+Shift+Enter and another Enter.

    If the formula still does not work, please upload an example sheet in Excel 2003 format (that is type xls).

  6. #6
    Registered User
    Join Date
    05-28-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    Okay, thanks for the feedback. I am traveling today and tomorrow so I won't be able to test this until Saturday when I return. But I was not aware of full column ranges not allowed in Excel 2003. So that's a good recommendation.

    Thank you!

  7. #7
    Registered User
    Join Date
    05-28-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    SOLVED !!!

    HansDouwe. . . I had to try your recommendation before I ran out the door and THAT WORKED! When I defined a specific range instead of a complete column, it is counting properly in Excel 2003.

    Thank you for your recommendation and help with my problem.

  8. #8
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    You are Welcome!

    Nice to hear that the formula with specific ranges in Excel 2003 also works well. I didn't know this.

    Thanks for the feedback and glad to have helped. .

    If you haven't already, please consider adding reputation to all helpers you think they deserve.
    In that case please click * Add Reputation left below their answers.

  9. #9
    Registered User
    Join Date
    05-28-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Converting a complex COUNTIFS formula into an equvalent (older) COUNTIF formula

    One last tidbit, now that I am back home and can review this further. The final solution was to use the SUMPRODUCT command instead of the basic SUM command. It was not necessary to make this an array formula (Ctrl + Shift + Enter). I am adding this information to help anyone else who might need a solution to this type of query in the older (2003) Excel software.

+ 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] converting a countifs referencing part of the formula to a indirect countifs
    By DEEARO in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2021, 03:17 PM
  2. Replies: 6
    Last Post: 10-11-2017, 04:57 PM
  3. [SOLVED] Complex Countif formula
    By thedeadzeds in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2014, 06:19 AM
  4. COUNTIF/COUNTIFS/IF Formula
    By quibilty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-11-2013, 03:17 PM
  5. [SOLVED] countif/sumif with more criterias - too complex for countifs/sumifs
    By Mengo85 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 07:18 AM
  6. Converting Countifs to 2003 formula
    By rgold in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 07-21-2011, 09:15 PM
  7. converting COUNTIFS formula to macro
    By batjl9 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2011, 03:37 AM

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