Formula that counts based on two different columns

Michael Rice

Hi there,

I feel as if this has been asked before, but I wasn't able to find a formula that fit my sheet.

I'm looking to have a column that simply places a "1" if two conditions are met in two other columns.

So basically: =COUNTIFS( [Column A = 100] AND [Column B =100] then place a 1.

There are a few other criteria's that need to be met too, like if the columns are specific to the @ row, and if they are all within the same week #, but I keep getting a zero.

So far, I have:

=COUNTIFS([Column A]:[Column A], [Column B]:[Column B]@row, [criteraA]:[criteraA], false, [CriteriaB:[CriteriaB], <>"", [Assignee Name]:[Assignee Name], [Assignee Name]@row)

But it keeps coming up blank, any help on where I messed up would be greatly appreciated!

Kelly Moore

Hey

Although you showed me 4 columns, it seems you are only interested in the two. I'll try this first.

=IF(AND([Customer Service Score]@row=1,[Knowledge Score]@row=1),1)

Since this is looking across a row, the data will automatically be in the same week since that is how your rows are arranged.

Is this the formula you were looking for? If not, let me know.

When you get to the point of gathering the data by person, or by weeknumber, I'd be happy to help. If the data is for a dashboard, consider exploring a report that is grouped by person and/or weeknumber. You can get the totals rolled up. If you have access to the PivotApp, you can also get totals by person

Kelly

Kelly Moore

Hey @Michael Rice

You will use a IF/AND formula with the COUNTIFS being the criteria within the AND function. I wasn't able to follow exactly what you were trying to make your COUNTIFS do - if you can provide more description I can help you write the COUNTIFS.

The structure will follow the outline below. If you need help with the countifs, I'd be happy to help. Remember COUNTIFS have the syntax of COUNTIFS(range1, criteria1, range2, criteria2, etc). The syntax must follow a range-criteria pair pattern.

=IF(AND(COUNTIFS([Column A]:[Column A], false, [Assignee Name]:[Assignee Name], [Assignee Name]@row, WeekNo:WeekNo, WeekNo@row=WEEKNUMBER(TODAY()))=100, COUNTIFS([Column B]:[Column B], <>"", [Assignee Name]:[Assignee Name], [Assignee Name]@row, WeekNo:WeekNo, WeekNo@row=WEEKNUMBER(TODAY()))=100),1)

Let me know where you are struggling and I will try to explain.

Kelly

Michael Rice

Thank you for your help so far @Kelly Moore!

To give you more context, I have two columns that are grades, (0-100) and I want another column to count (or mark) a "1" for every 100 in both columns (both columns must have a 100 score for the column to have a 1, otherwise it would be a 0).

I'm grading tech's solved tickets, and if they score a 100 in both categories I want to mark a 1 so I can keep track at how many times each Tech gets a 100/100 within the week.

I have a column for the week number as well, so I want to make sure it basically says "if these two columns have a 100 in them, and they are within the same week number that's in another column, place a 1 here"

I'm struggling with the formula counting both 100's and adding it as 1 in the column, If/and does make sense, but I wasn't sure if you could combine formulas in Smartsheets.

Kelly Moore

Hey Michael

I'm happy to help you with this. Is it possible to see a screenshot of the columns with a bit of data in it so I can see what you're working with? Please obscure any sensitive info

Michael Rice

Not a problem! I have to use two different ones since my sheet is so big , but the second one highlights the two columns I want to count (if there is a 100%) and the first one shows the other qualifiers that I want to include (if the score is in the same week # and if the "not audited" column is unchecked/false

Kelly Moore

Hey

I have a few questions to clarify, please

If the formula is going into the 'Perfect Score Count', are you wanting this count to be for each row, or, looking at the 3 rows you have highlighted, do you want a 3 to be for all three (so only listed once for all 3 rows? If you are keeping a running tally of total perfects in the sheet, a one for each row would work. Either of these options is possible - they are just different solutions.

Michael Rice

The latter, I would like for it to count a "perfect score" as 1, for each row (all three as well as the entire column as I go through and grade) So basically a running tally so I can know how many times a tech scored a "perfect score". Ideally the column will have a "1" and then i'll create another sheet that tallies that by the individual tech.

Kelly Moore

Hey

Although you showed me 4 columns, it seems you are only interested in the two. I'll try this first.

=IF(AND([Customer Service Score]@row=1,[Knowledge Score]@row=1),1)

Since this is looking across a row, the data will automatically be in the same week since that is how your rows are arranged.

Is this the formula you were looking for? If not, let me know.

When you get to the point of gathering the data by person, or by weeknumber, I'd be happy to help. If the data is for a dashboard, consider exploring a report that is grouped by person and/or weeknumber. You can get the totals rolled up. If you have access to the PivotApp, you can also get totals by person

Kelly

Michael Rice

Thank you so much Kelly! That did resolve my question. I can take it from here :)