CountIF formula for two different columns??

I'm looking to find a way to create a count statement if one column contains "a" and the other column contains "b".

Example: If "Status" is "Open" and "Issue Type" is "Bug",then count #

I looked to the formula example sheet for this but all I see in one that I believe would work has a "expand rows to see example" message where the formula would be???

Find more posts tagged with

Healthcare

Formulas

IT & Operations

Comments

Andrew DeCounter

Greetings,

There are a couple of ways to do this, but this example should meet your needs using the COUNTIFS function.

CountIf Status is "Open" AND Triage result is "Bug"

=COUNTIFS(Status1:Status10, Status1:Status10 = "Open", Triage1:Triage10, Triage1:Triage10 = "Bug")

open bugs example

Hope this helps!

J. Craig Williams

Andrew,

Your COUNTIFS is over complicated.

This is better

=COUNTIFS(Status1:Status10, "Open", Triage1:Triage10, "Bug")

Craig

adrian.ritchie28856

Hi there,

Are you able to look at my formula?

=COUNTIFS(Story Points1:Story Points129, >0, [Sprint Estimate1]:[Sprint Estimate129], "52")

I have a column of Story Points and a column where I estimate the Sprint that task will enter. I want to know how many story points estimated per sprint. If I enter 52 into several columns, then create a filter, I want to be able to see the total "Story Points" for Sprint 52.

adrian.ritchie28856

See screenshot.

Screen Shot 2017-11-21 at 11.44.11 AM.png

J. Craig Williams

I don't think I know enough to give the exact answer.

This is better than your example

=COUNTIFS([Story Points]1:[Story Points]129, >0, [Sprint Estimate]1:[Sprint Estimate]129, 52)

1. The references cells - [COLUMN]ROW where the [] are needed when the column has spaces, special characters, or starts/ends with a number.

2. I changed "52" to 52 as the first is text and the second is a number. They are NOT the same and need to be treated differently.

Now, as to why I can't give you a better answer:

a. You probably don't want to be COUNTING, but rather SUMMING the values in the [Story Points] column.

b. You have two sprints listed in row 127. This won't be found when looking for either 48 or "48" because neither of these match "48, 49" which will be text.

If you can not limit the Sprint Estimate to a single sprint, then you may want to look into using FIND() instead. But ... that's going to run into problems when looking at "5" when there are rows with "52" and the problem of text vs numbers again.

Good luck.

Craig

caitlinoreilly11

Is it possible to do this while reference two columns in another sheet?

J. Craig Williams

Yes it is possible to reference two or more columns in the another sheet (as of 2018-02-06)

Craig

caitlinoreilly11

thank you! i have gotten the below formula to work on the sheet it is already one, but it doesnt work when im in a new sheet trying to reference that sheet - any idea how to do that?

columns "message or asset" and "designer" are near eachother - do they need to be?

=COUNTIFS([Message or Asset]:[Message or Asset], "placement", Designer:Designer, "NAME")

J. Craig Williams

The columns do not need to be near each other.

The syntax will look something like this when you are done:

=COUNTIFS({rangename1},"placement",{rangename2},"NAME")

If you start from this:

=COUNTIFS( HERE ,"placement",{rangename2},"NAME")

and put your cursor over the "HERE" part, you should see something that says "Reference Another Sheet". It will look like (and be) a hyperlink to setup the X-Sheet Reference. The name of the range you select if yours to decide.

I use something like

SHEET NAME | COLUMN NAME

or some short hand

Action Items | Status

for example, for the Action Items sheet's column named status. But it could be Bob's Ref 1. Just as long as it is unique.

It really is easy one you successfully do it once.

Craig

caitlinoreilly11

Hi - this seems to return as unparsable, here is what i am using:

=COUNTIFS({NEW Global Messaging 2018 Range 2}, "placement", {NEW Global Messaging 2018 Range 3} "NAME")

J. Craig Williams

Looks like you are missing a comma before the last "NAME"

Craig

caitlinoreilly11

thank you!!! it works.

is it possible to reference 3 columns - like this? its not returning the actual count

=COUNTIFS({NEW Global Messaging 2018 Range 2}, "placement", {NEW Global Messaging 2018 Range 3}, "NAME", {NEW Global Messaging 2018 Range 4}, "3/13/18")

J. Craig Williams

Yes.

If {NEW Global Messaging 2018 Range 4} references a Date type column, you'll want to use something that returns a date. Either the DATE() formula

...}, DATE(2018,03,03))

or a cell containing the date.

Craig

caitlinoreilly11

the range contains a column that is only dates, but different dates.

is this how it should look?

=COUNTIFS({NEW Global Messaging 2018 Range 2}, "placement", {NEW Global Messaging 2018 Range 3}, "NAME", {NEW Global Messaging 2018 Range 4}, DATE(2018, 3, 3))

J. Craig Williams

That should count Mar 3rd 2018 rows that satisfy the other criteria.

Craig