Countif Valve is in Several Different Locations

[Timberwolf]

Hi all

What I'm looking to do is on Sheet3 cell C2 I want a formula to check six different rows on Sheet1 to see if they match cell A2 on Sheet3 and each time it matches add 1.

I have the code looking in one row as follows =COUNTIF(Sheet1!J:J,[@[Item '#]]) but I also want it to check SHEET1 rows L:L N:N P:P R:R and T:T

I don't want them all to match, I want it to count 1 if any of them match

So if Sheet1 cell J2, L2, N2, and P2 match [Item '#] there would be 4 in cell C2 on Sheet3 or if it was only in L2, N2, and P2 it would have a value of 3.

I hope someone can understand this and help me out, I know I may not be explaing myself properly.

 

[jtakw]

Hi,

If you can't group the Columns (not rows) together like J:T because of other data in between that might throw off your count, then use SUM to add up the counts, like:

=SUM(COUNTIF(Sheet1!J:J,[@[Item '#]]),COUNTIF(Sheet1!L:L,[@[Item '#]]),COUNTIF(Sheet1!N:N,[@[Item '#]]),COUNTIF(Sheet1!P:P,[@[Item '#]]),COUNTIF(Sheet1!R:R,[@[Item '#]]),=COUNTIF(Sheet1!T:T,[@[Item '#]]))

Also, try not to use Entire Column references whenever possible, maybe use a range that you know is larger than the data will ever be, like J1:J10000

 

[Timberwolf]

Thank you So much that worked great (small edit to the last countif code. had to delete the "=" sign) Just curious why I should not use the entire Column reference?

 

[jtakw]

You're welcome, and oh yeah, missed deleting that last = sign, I copied and pasted your COUNTIF formula 6 times, then updated the Column references.

About the entire column reference, that's because there are over a Million rows in Each column, so it's Always good practice to not use entire column references as it will slow down calculations.

 

[Timberwolf]

OK, new problem how do I copy the code down without the numbers changing EG C2 says L2:L10000, C3 says L3:L10001, C4 says L4:10002.

Sorry I'm sure that is basic Excel but I'm new to the programming part. Boss said can you make me a table that does this and this and this? Sure can! lol and off to Professor Google I went lol.

 

[jtakw]

Use the $ to lock Column and/or Cell references, in this case, like this: L$1:L$10000

 

[Timberwolf]

Again Thank you, worked like a charm!

 

[jtakw]

You're very welcome, post again whenever you need help.