Can I nest Countifs Statements in an IF / OR statement?

**CWatsonJr** · 10-02-2013, 01:08 PM

I am trying to count how many times certain letter combinations occur in a column based on the date and another geographical code. Here is what I came up with but the result is giving me FALSE as the return and it should be giving me 1 (not for true but for one occurrence).

=IF(OR('2013 Data'!$G:$G=G13,'2013 Data'!$G:$G=G14,'2013 Data'!$G:$G=G15,'2013 Data'!$G:$G=G16,'2013 Data'!$G:$G=G17,'2013 Data'!$G:$G=G18),(COUNTIFS('2013 Data'!$I:$I,$A13,'2013 Data'!$D:$D,"ABC")+COUNTIFS('2013 Data'!$I:$I,$A13,'2013 Data'!$D:$D,"ACD")+COUNTIFS('2013 Data'!$I:$I,$A13,'2013 Data'!$D:$D,"ADF")+COUNTIFS('2013 Data'!$I:$I,$A13,'2013 Data'!$D:$D,"DFG")))

The '2013 Data' sheet holds all of my Data, Column G on this sheet holds the geography code, Column I holds the date of occurrence, Column D holds the letter combinations

There are six geographical codes I am asking for the data to occur in, those codes are in G13:G18
The date I am asking it to look at is in A13

I know that "DFG" occurs one time for the date I am asking for and in one of those six geographic areas but the formula is returning FALSE.

Ideas?

Thank you in advance for your help!!

**Ace_XL** · 10-02-2013, 01:18 PM

The first part of your formula is equating to FALSE

Please Login or Register  to view this content.

Change your formula to

Please Login or Register  to view this content.

**TMS** · 10-02-2013, 01:22 PM

If you space the formula out, you can see that you only have a TRUE result and no FALSE result. So, if none of the OR conditions is TRUE, you will get FALSE returned.

Formula:

Please Login or Register  to view this content.

Oh, and it's unlikely that you are going to get a whole column equal to one cell as in

Formula:

Please Login or Register  to view this content.

Regards, TMS

**bebo021999** · 10-02-2013, 01:26 PM

Dear all,
I afraid that OR(...) allways TRUE because G13,G14,...,G18 are always exist in G:G

Sure that the formula can be shorten much, if the sample file is able to be posted.

**CWatsonJr** · 10-02-2013, 02:49 PM

With the first suggested formula fix, it seems it is not eliminating data outside the geocode areas. I have posted a sample sheet. The first date on the Summary sheet should have 4 and not 6.

Thank you again for your help.

**Ace_XL** · 10-02-2013, 02:56 PM

Shouldn't the answer be 3?

Try

Please Login or Register  to view this content.

**CWatsonJr** · 10-02-2013, 02:57 PM

Originally Posted by TMShucks

Oh, and it's unlikely that you are going to get a whole column equal to one cell as in

Formula:

Please Login or Register  to view this content.

I can use COUNTIF('2013 Data'!G:G,G13) to count how many times the contents of G13 occurs in Column G of the data sheet. Using that same logic, can I not use IF('2013 Data'!G:G=G13, "FindMe") to create a TRUE condition when the contents of G13 are found?

**CWatsonJr** · 10-02-2013, 03:01 PM

Originally Posted by Ace_XL

Shouldn't the answer be 3?

Try

Please Login or Register  to view this content.

UH... yes. 3 is correct - I hand counted wrong.

**CWatsonJr** · 10-02-2013, 03:08 PM

Ace - since my actual data sheet is endless (over 5 thousand rows of data) and changes in actual length from week to week, limiting the formula to row numbers will not work. When I changed the formula to look at the whole column (i.e. '2013 Data'!$I:$I) Excel had a fit and told me it ran out of resources and couldn't calculate the formulas.

Another idea?

**TMS** · 10-02-2013, 03:22 PM

Put 1,2,3,4,5 in G13, G14, G15, G16 and G17 respectively and put 1,2,3,4,5 on Sheet '2013 Data'!$G:$G, anywhere you like. Put the formula:

Formula:

Please Login or Register  to view this content.

anywhere you like on the original sheet (where you populated G13:G14). You get "no".

You are trying to compare an entire column to a single cell. That's not the same as a COUNTIF. If you said:

Formula:

Please Login or Register  to view this content.

you will get "yes".

I can't explain it a different way.

Regards, TMS

**TMS** · 10-02-2013, 03:28 PM

You can define Dynamic Named Ranges for the columns you are interested in:

nrType:

Formula:

Please Login or Register  to view this content.

nrGEO:

Formula:

Please Login or Register  to view this content.

Then refer to the named ranges rather than fixed cell references.

Regards, TMS

**Ace_XL** · 10-02-2013, 03:33 PM

Another alternative if you want to keep referring to entire columns

Please Login or Register  to view this content.

**CWatsonJr** · 10-02-2013, 03:42 PM

Originally Posted by Ace_XL

Another alternative if you want to keep referring to entire columns

Please Login or Register  to view this content.

Perfect!!!!!!! Thank you very much.

You guys here are so awesome!!!!!!!!!

**TMS** · 10-02-2013, 03:44 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**CWatsonJr** · 10-02-2013, 03:46 PM

Thanks TMShucks - I was hunting around on the page trying to remember how to mark it as solved.

**TMS** · 10-02-2013, 04:13 PM

You're welcome. Thanks for the rep.

Can I nest Countifs Statements in an IF / OR statement?

LinkBack

Thread Tools

Rate This Thread

Display

Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Re: Can I nest Countifs Statements in an IF / OR statement?

Thread Information

Users Browsing this Thread

Similar Threads

Countifs with nest left

[SOLVED] Can I nest IF statements, if the first one is checking for data?

[SOLVED] How to nest two IF statements

Nest Lookup With If And Statements

[SOLVED] How do I nest IF statements to only return value if both true?

Bookmarks

Bookmarks

Posting Permissions