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

1. ## 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.

2. ## 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.

Formula:
`Please Login or Register  to view this content.`

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

May be this

Formula:
`Please Login or Register  to view this content.`

4. ## 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. ## 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:
`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. ## 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. ## 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. ## 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. .

9. ## 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.

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