#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# CountIF formula for two different columns??

Options
edited 12/09/19

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???

• edited 08/28/17
Options

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")

Hope this helps!

• ✭✭✭✭✭✭
Options

Andrew,

This is better

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

Craig

• Options

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.

• Options

See screenshot.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

Craig

• Options

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")

• ✭✭✭✭✭✭
Options

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

• Options

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")

• ✭✭✭✭✭✭
Options

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

Craig

• Options

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")

• ✭✭✭✭✭✭
Options

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

• Options

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))

• ✭✭✭✭✭✭
Options

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

Craig

This discussion has been closed.