COUNTIF two columns match return total from a third column
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})
Answers

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?

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

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.

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

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?

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

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 crosssheet references, you can name them at the top of popup where you select which range to reference. This would keep your names shorter and more identifiable when writing your formula.

Thank you so much, I will give it a try
Help Article Resources
Categories
Check out the Formula Handbook template!