Combining two working formulas

John Stanik

Hi,

I am horrible at Excel and formulas 😭. I have two formulas that work correctly on their own:

=COUNTIF(Event:Event, CONTAINS("Prime", @cell))

=COUNTIF(Primary:Primary, <>"")

I'm trying to combine these two formulas to get a count if column Primary is not blank AND if a cell in column Event CONTAINS the word prime. The word prime can be alone, or part of a string of words.

I've tried a couple of options:

=COUNTIFS(Primary:Primary, <>"", Event:Event, CONTAINS("Prime", @cell)) - this gets a result of zero

=COUNTIFS(Primary:Primary, <>"", Event:Event, FIND("Prime", @cell)) -this gets #invalid data type

Any assistance would be much appreciated!

John

Kelly Moore

Hey @John Stanik

=IF(COUNTIFS(CHILDREN(Event@row), OR(CONTAINS("Prime", @cell), CONTAINS("Early", @cell))) > 0, 1)

This is the correct syntax to use your formulas

John Stanik

@Kelly Moore

That did it! Thanks so much for your time, patience and consideration!

Best,

John

Edward Spencer

Try this: =COUNTIFS(Primary:Primary, <>"", Event:Event, ="Prime")

If you are doing several counts or sums, etc. I always use an second sheet I call "Metrics" to contain all of this data. You can then point to the metrics sheet for your dashboard widgets, etc.

John Stanik

Thanks Edward,

That also gives me a count of zero. I may have omitted part of the ask (or simply didn't explain very well). The cells in the column for Event are free text... I'm trying to find any of those cells that have an instance of the word "Prime" in them. Would that be more of a FIND function?

Also, I'm creating this in the sheet summary. Do you still think I would need a second sheet?

Thanks for the quick reply!

Best,

John

John Stanik

@Edward Spencer Sorry Edward, I'm not sure if I'm supposed to @ mention you my response, please see my previous comment.

Thanks,

John

Edward Spencer

John,

Yes, I did not understand at first what you were doing. I got tied up with my own projects, but I think this may work.

Try putting this in the summary box:

=COUNTIFS(Primary:Primary, <>"", (Event:Event), CONTAINS("prime", @cell))

Let me know if this works for you.

-Edward

John Stanik

@Edward Spencer

Hi Edward,

Thanks for the reply!

Still got a "0". I've attached a screenshot of what I'm trying to get. What I need is the count of the cells that contain the word "Prime" in them. So, in this screenshot the total would be 3. So I think this may be a Parent/Child formula? I need a count of the parent in the Primary Column that has children in the Event column with "Prime" contained in its cell.

I'm sure this is not making any sense, so apologies in advance...

John

2021-12-03_14-22-36.png

John Stanik

@Edward Spencer

I'm thinking along the lines of this:

=COUNTIF((Primary:Primary, <>"") AND(CHILDREN(Event@row, CONTAINS(“Prime”,@cell))))

Edward Spencer

I did not know about the parent/child part. The formula works for me when I duplicate your spreadsheet WITHOUT parent/child relationships. This is something that I do not know how to do off-hand, I may be able to figure it out. There maybe others who can answer faster on the forum.

John Stanik

@Edward Spencer

Sorry for the confusion - I'm struggling with even trying to explain it. Thanks so much for taking a look. As long as this thread is unanswered, others will possibly look at it, right?

Kelly Moore

Hey @John Stanik

I am a bit confused at your request- are you looking for an answer of 1 Parent row as the count, or three rows that contain "Prime". If three is the answer you need then your first formula should work. If one is the answer, then I would add a helper column that could indicate the parent row had children containing 'Prime". The your summary field would count responses in that helper column.

For instance, if you had a checkbox helper column

=IF(COUNTIFS(CHILDREN(Events@row),CONTAINS("Prime",@cell))>0,1)

This could be a column formula and will automatically add a checkmark to each parent row that has at least one 'Prime' in it as a Child row. This helper column is what you would count in your summary field.

Does this work for you?

John Stanik

@Kelly Moore

Hi Kelly,

Thanks for deciphering my question. That is exactly what I needed and it worked perfectly!!!

I was just asked to also exclude any cells that contain the word "Early" too. So I believe I need an OR statement in there. These two formulas do work independently:

=IF(COUNTIFS(CHILDREN(Event@row),CONTAINS("Prime",@cell))>0,1)

=IF(COUNTIFS(CHILDREN(Event@row),CONTAINS("Early",@cell))>0,1)

I tried these four, but no luck:

=IF(COUNTIFS(OR(CHILDREN(Event@row),CONTAINS("Prime",@cell), CHILDREN(Event@row),CONTAINS("Early",@cell))>0,1)

=IF(OR(COUNTIFS(CHILDREN(Event@row),CONTAINS("Prime",@cell)), (COUNTIFS(CHILDREN(Event@row),CONTAINS("Early",@cell)))>0,1)

=IF(COUNTIFS(CHILDREN(Event@row),CONTAINS(OR("Prime",@cell, “Early, @cell)))>0,1)

=IF(COUNTIFS(CHILDREN(Event@row), OR(CONTAINS("Prime",@cell)), (CONTAINS(“Early”,@cell)))>0,1)

Thank you again!!!

John

Kelly Moore

Hey @John Stanik

I believe you stated you wanted to exclude that criteria, therefore you are only counting when the cell does not contain the phrase 'early'. If this is a misinterpretation, remove the NOT and related parentheses from the formula. We could write this as an OR statement however the nested parentheses can be a pain so I chose to write them separately.

=IF(COUNTIFS(CHILDREN(Events@row),CONTAINS("Prime",@cell), CHILDREN(Events@row),NOT(CONTAINS("Early",@cell)))>0,1)

Shout out to me if this doesn't work for you and we'll get it right

cheers

Kelly

John Stanik

Hi @Kelly Moore

I'm so sorry if I'm confusing things with my attempt to describe this... I'm actually a nurse (RN), not a data/spreadsheet/Excel person 😁 (as if you couldn't already tell).

The formula above works and gives me a "1" in parent rows that the word Prime is in the Event column. I also need that "1" in the helper column if the Event column contains the word "Early". I think you were saying to remove the NOT and related parenthesis to do that. I tried that with this formula:

=IF(COUNTIFS(CHILDREN(Event@row), CONTAINS("Prime", @cell), CHILDREN(Event@row), CONTAINS("Early", @cell)) > 0, 1)

I don't get an error with it, but I don't get any 1's in the helper column (they're all blank). Is this working as an "and" statement as in - must contain both Prime & Early? Should there be an OR somewhere in there?

Again, so sorry - thank you so much for looking at this!

Best,

John

John Stanik

Hi @Kelly Moore

I think I got it:

=IF(COUNTIFS(CHILDREN(Event@row), NOT(CONTAINS("Prime", @cell)), CHILDREN(Event@row), NOT(CONTAINS("Early", @cell))) > 0, 1)

It gives me the opposite numbers in the helper column but I can just exclude the "blanks".

Thank you so much for your time and patience!!!!

Best,

John

Kelly Moore

Hey @John Stanik

I just realized it has to be an OR. Silly me - I was working on my own stuff and not paying attention.

=IF(COUNTIFS(CHILDREN(Event@row), OR(CONTAINS("Prime", @cell), NOT(CONTAINS("Early", @cell))) > 0, 1)

As I had it written it would have looked for a row that simultaneously met both conditions. Since the conditions occur from one column it probably was only working when Event contained Prime.

I still am not clear if you are eliminating 'early' or not.

See if this works better

Kelly

John Stanik

Hi @Kelly Moore

That gives me a "#INCORRECT ARGUMENT" error.

I tried to manipulate it with this:

=IF(COUNTIFS(CHILDREN(Event@row), OR(CONTAINS("Prime", @cell), (CONTAINS("Early", @cell))) > 0, 1)

but that didn't work either.

Right now I'm using the formula below and getting one's and zero's in my helper column:

=IF(COUNTIFS(CHILDREN(Event@row), NOT(CONTAINS("Prime", @cell)), CHILDREN(Event@row), NOT(CONTAINS("Early", @cell))) > 0, 1)

But is that not counting as an "or" statement?

John