COUNTIF two columns match return total from a third column

Connie Cochran

I am trying to say if column in another sheet is the same as primary column count the total text in a third column but I'm getting an error, any suggestions as to what I'm doing wrong?

=COUNTIF({Testing Request Form Range 1}, =[Primary Column]@row, {Testing Request Form Range 4})

David Tutwiler

You don't need the equal sign, it is assumed in the COUNTIF formula. Try:

=COUNTIF({Testing Request Form Range 1}, [Primary Column]@row, {Testing Request Form Range 4})

If that doesn't work, can you post the error you are getting?

Connie Cochran

Hi David, Thank you for your help. I tried the formula above, and the error is the same "#incorrect argument set"

David Tutwiler

Ah, I am sorry, I see the bigger issue now. I was focused on the = sign too much.

A COUNTIF function only has 2 arguments. The range to evaluate and the evaluation criteria. For each match, it will add 1 to the count. So if you remove the 3rd parameter, you would get a count of how many instances matched your criteria.

If you need to do another operation on the data, like say Summing up the information in another column, then I would suggest using that formula. Sum allows you to evaluate one column, then sum from another. Something like:

=SUMIF({Testing Request Form Range 1}, [Primary Column]@row, {Testing Request Form Range 4})

Where the Testing Request Form Range 4 is your Sum column and Range 1 is your evaluation column.

Connie Cochran

All 3 columns have text not numbers. Is there another formula I can use? If column1 is equal to column2 then count column3

David Tutwiler

I am having trouble understanding the context. If all 3 columns have text, then what would be the difference in counting the times column 1 is equal to column 2, and counting column 3?

Could you post a screenshot, by chance?

Connie Cochran

Sorry to be difficult. This one really has me stumped. There are 2 different sheets.

Sheet 1 - primary column is a list of state abbreviation which needs to match Sheet 2 state abbreviation and then count all the number of comments in comments column on sheet 2 for that state abbreviation.

I tried the following but now I'm getting #invalid data type.

=IF({Testing Request Form Range 1}, =[Primary Column]@row, (COUNT({Testing Request Form Range 2})))

David Tutwiler

No problem at all, just trying to make sure I fully understand what you're looking for here.

I think the best way to attack this would be with a helper column. On Sheet 1, you could add a column that counts the number of comments in the comment column. I'll call this column Comment Count. Then you would run the SUMIF formula to add them all up, if the abbreviations match. So it would look like:

=SUMIF({Testing Request Form Range 1}, [Primary Column]@row, {Testing Request Form Range 5})

Where Range 5 is the new helper column.

Also, just as a note, when you are creating cross-sheet references, you can name them at the top of pop-up where you select which range to reference. This would keep your names shorter and more identifiable when writing your formula.

Connie Cochran

Thank you so much, I will give it a try