Want to streamline a COUNTIF formula

[tb813]

I'm doing a COUNTIF for various keywords in multiple non-adjacent columns. So right now, this is what I have:

=COUNTIF(Sheet2!A:A,"Blank")+COUNTIF(Sheet2!F:F,"Blank")+COUNTIF(Sheet2!K:K,"Blank")+COUNTIF(Sheet2!P:P,"Blank")+COUNTIF(Sheet2!U:U,"Blank")+COUNTIF(Sheet2!Z:Z,"Blank")+COUNTIF(Sheet2!AE:AE,"Blank")

The problem is that I'm be gonna be doing the formula almost a hundred different times, with the "Blank" representing a different keyword in each one. Is there some way to streamline this so I only have to change the blank once for each one?

[Tony Valko]

If I understand what you're asking...

Use a cell to hold "blank" then just refer to that cell.

That way all you have to do is change the cell entry and not the formula.

A1 = blank

=COUNTIF(Sheet2!A:A,A1)+COUNTIF(Sheet2!F:F,A1)+COUNTIF(Sheet2!K:K,A1)+COUNTIF(Sheet2!P:P,A1)+COUNTIF(Sheet2!U:U,A1)+COUNTIF(Sheet2!Z:Z,A1)+COUNTIF(Sheet2!AE:AE,A1)

[Richard Buttrey]

Hi,

I don't understand.

When you say the formula will be repeated almost a 100 times, what is 'the formula'. Is that the whole of the series of COUNTIF() functions or is each COUNTIF() a 'formula'.

You say you want to replace the "Blanks' wit a different keyword but then say you only want to change the blanks once.

It would help if you uploaded a workbook with some examples and manually add the results you expect to see.

[tb813]

I think I can try to make this more clear without uploading a whole workbook. There will be lists of names that appear in columns A, F, K, P, U, Z, and AE. So, let's say I have the names Tom, Rick, and Harry. I can post the following formulas in separate cells...

=COUNTIF(Sheet2!A:A,"Tom")+COUNTIF(Sheet2!F:F,"Tom")+COUNTIF(Sheet2!K:K,"Tom")+COUNTIF(Sheet2!P:P,"Tom")+COUNTIF(Sheet2!U:U,"Tom")+COUNTIF(Sheet2!Z:Z,"Tom")+COUNTIF(Sheet2!AE:AE,"Tom")

=COUNTIF(Sheet2!A:A,"Rick")+COUNTIF(Sheet2!F:F,"Rick")+COUNTIF(Sheet2!K:K,"Rick")+COUNTIF(Sheet2!P:P,"Rick")+COUNTIF(Sheet2!U:U,"Rick")+COUNTIF(Sheet2!Z:Z,"Rick")+COUNTIF(Sheet2!AE:AE,"Rick")

=COUNTIF(Sheet2!A:A,"Harry")+COUNTIF(Sheet2!F:F,"Harry")+COUNTIF(Sheet2!K:K,"Harry")+COUNTIF(Sheet2!P:P,"Harry")+COUNTIF(Sheet2!U:U,"Harry")+COUNTIF(Sheet2!Z:Z,"Harry")+COUNTIF(Sheet2!AE:AE,"Harry")

...And that will get me the counts of how many times those three names appeared in the relevant columns. But if I have a hundred different names that I want to count, then I have to change the name in that formula a hundred different times, and I'd like to find a way to make that process a little less tedious. Does that make more sense?

[loginjmor]

Hi -

Tony Valko is spot on with using a cell to easily change your search criteria rather than hard coding it into your formula. You could also use SUMPRODUCT and get rid of all the COUNTIF's such as:

=SUMPRODUCT(--(Sheet2!A:A,$A$1)+--(Sheet2!F:F,$A$1)+--(Sheet2!K:K,$A$1)+--(Sheet2!P:P,$A$1)+--(Sheet2!U:U,$A$1)+--(Sheet2!Z:Z,$A$1)+--(Sheet2!AE:AE,$A$1))

[Tony Valko]

Still not clear (to me).

Does that mean you want to do something like this:

=COUNTIF(Sheet2!A:A,100 names)+COUNTIF(Sheet2!F:F,100 names)+ etc., etc.

[loginjmor]

Hi -

Just saw your last post. I still think Tony Valko has the best approach. Just use Row 1 to put your list of names (A1, B1, C1 are Tom, Rick Harry, etc.)
Put your formula in A2 and just make the address reference relative (e.g., no $A$1) and copy to the right. Easy!

[tb813]

Hi -

Just saw your last post. I still think Tony Valko has the best approach. Just use Row 1 to put your list of names (A1, B1, C1 are Tom, Rick Harry, etc.)
Put your formula in A2 and just make the address reference relative (e.g., no $A$1) and copy to the right. Easy!

I didn't understand what he was saying at first, but it's clicked for me now, and that does make a lot more sense. But, can you tell me how SUMPRODUCT as better than COUNTIF? The formula doesn't look any more streamlined to me.

[loginjmor]

Hi -

SUMPRODUCT isn't better than COUNTIF. You just don't have to repeat the COUNTIF text over and over again. Just a little easier to read. At this point since you have your formula set up, I would just go with it.