COUNTIFS/CONTAINS FUNCTION

Bradley Lindsay · 07/08/20

I am having a similar but perhaps slightly different issue. I am wondering when using the Countifs function am i able to use 3 sheet references and count the unique number of times a month is occuring for a particular Event Type within a event topic.

e.g. Formula which only returns if there the month exists not how many times.

Summary Sheet Data

Source Sheet Data

Apologies if this is unclear, I am trying to summarize how many times an Event Topic happens per month wit a particular communication medium.

thans,

Paul Newcome · 07/08/20

@Bradley Lindsay Your current formula should be working. Are you able to provide a screenshot that shows the source data where the COUNTIFS is producing the wrong number?

Desirae Borge · 07/10/20

I'm not able to get my formula to work either. What am I doing wrong? They both say 0

Paul Newcome · 07/10/20

@Desirae Borge Replace your second range reference with @cell.

Davidp · 08/11/20

I have a follow up question to the initial countifs and contains question/solution.

First after finding this string, I found the solution to exactly what I was looking for on a single possible criteria.

My question is how do I, if possible write a function that will search for 2 possibilities

=COUNTIFS(Sunday2:Sunday14, CONTAINS("0600-1400", @cell))

I am working on a sheet that will auto count total staff scheduled on any given day when the schedule is filled in. Each column is a day of the week. With the string noted above, it works great however does not count total staff as we have two schedules for the shift I am working on, 0530-1330 and 0600-1400. When I use the string above for the 0600-1400 schedule it works as expected; however how would I introduce the second schedule to the same string to make the count for any cells that contain 0530-1330 or 0600-1400.

The individual cells unfortunately do not only contain the scheduled hours but also a location assignment to various locations thus the "contains" need.

Paul Newcome · 08/12/20

@Davidp Try this...

=COUNTIFS(Sunday2:Sunday14, OR(CONTAINS("0600-1400", @cell), CONTAINS("0530-1330", @cell)))

Amber Jackson · 02/02/21

@Paul Newcome If you are ever in South Carolina, I owe you a drink--you saved my sanity with @row, @cell!!

Paul Newcome · 02/03/21

@Amber Jackson Haha. Deal. I am in West Virginia though, so that's a little bit of a hike for me. At least it is still east coast. Hahaha

Katie Williams · 02/03/21

@Paul Newcome WV in the house!! 💛 #almostheaven

MaryAnn Amari · 02/05/21

Hi everyone - I'm trying to countif entries of part numbers contain a "c" in them. For example 12c12345 or 12C12345 or 12354c vs not counting any part number entry that doesn't include a C. Any way to do that? This isn't cutting it: =COUNTIF(MyColumnName:MyColumnName, CONTAINS("c", @cell)).

Thanks in advance.

Paul Newcome · 02/08/21

If the string does not contain a letter in it, then it is probably being stored as a number as opposed to a text string. This means that your data is not all formatted the same (numbers in some cells and text in the other). Insert a new column and enter

=MyColumnName@row + ""

And set that as a column formula. This will convert everythign into a text string even those that do not contain a letter. You can then use this new column in your COUNTIFS, and it should be working for you.

Clark Coles · 05/10/21

Hello @Paul Newcome . I'm hoping you can help me with an issue i'm having using COUNTIFS with CONTAINS.

I'm trying to count all the instances where a check box is checked off in a checkbox type column and the job title contains "manager". Here is the formula I'm using that I think should be working based on everything I've read, but it is returning #INCORRECT ARGUMENT and I'm not sure why.

The "Seller 1:1 (NAM)" is the checkbox column title, and the "Job Title" is the column title for the other range.

=COUNTIFS([Seller 1:1 (NAM)]:[Seller 1:1 (NAM)], =1, [Job Title]22:[Job Title]257, CONTAINS("manager", @cell))

Paul Newcome · 05/10/21

@Clark Coles It is because your first range is the entire column but the second range is rows 22 - 257. Try correcting this so that both ranges are the same size and see if that clears up your problem.

Jenn Pitt · 06/14/21

=COUNTIFS({{Customer Range Complete}, Find("Active [In-Progress]"), {{{Customer Range Complete}}, FIND("Greenfield")} I am getting an unparseable error on this formula trying from same worksheet count the number of one range only if on other columns it has Active and Greenfield

Paul Newcome · 06/14/21

@Jenn Pitt If it is in the same sheet, you will need to use

[Column Name]:[Column name]

as your ranges.

The columns that contains "Active" and "Greenfield"... Do those cells have additional text within them or is it only the word "Active"/"Greenfield" in the cells?

COUNTIFS/CONTAINS FUNCTION

Answers

Help Article Resources

Categories