Thread: Countif funtions problem

I have the following Countif formula working but I wish to add many more columns to the function but excel says I can't do that. Is it possible or do I use another function? Thanks for all your help.

Mike

WORKS
=COUNTIF(Q21:Q128:R21:R128,"1-*")

DOES NOT WORK
=COUNTIF(Q21:Q128:R21:R128:V21:V128:W21:W128:AA21:AA128:AB21:AB128:AF21:AF128:AF21:AG128:AG21:AK128: AK21:AL128:AL21:AP128:AP21:AQ128:AQ21:AU128:AU21:AV128:AV21:AZ128:AZ128:BA128:BA128:BE128:BE128:BF12 8:BF128:BJ128:BJ128:BK128:BK128,"1-*")

Try

=SUMPRODUCT((LEFT(Q21:BK128,2)="1-")*(MOD(COLUMN(Q21:BK128)-COLUMN(Q21),5)<2))

Works great but I have 1 small problem. In the string you provided is it possible to change the 1-* to add another line that has a value of say 10 or 11?

Thanks again for all your help.

Mike

Below I have the following Sumproduct formula working but I wish to add “10-* or 11-* to the function but it doesn’t work. I started with the countif formula below. The problem was to increase the number of columns that was being counted. daddylonglegs solved the problem with the Sumproduct formula but I’m also needing is “10-* or 11-* numbers to be searched.

Also, I'm using the formula below to find "1-*or 1-10-*" value but I would also like to determine the exact cell location and the result to show up in cell c8. The formula below is located in cell d8 if that matters.

Thanks for all your help.

Mike

Orginal problem

=COUNTIF(Q21:Q128:R21:R128:V21:V128:W21:W128:AA21: AA128:AB21:AB128:AF21:AF128:AF21:AG128:AG21:AK128: AK21:AL128:AL21:AP128:AP21:AQ128:AQ21:AU128:AU21:A V128:AV21:AZ128:AZ128:BA128:BA128:BE128:BE128:BF12 8:BF128:BJ128:BJ128:BK128:BK128,"1-*")


Below is what I'm using and it works

=SUMPRODUCT((LEFT(Q21:BK128,2)="1-")*(MOD(COLUMN(Q21:BK128)-COLUMN(Q21),5)<2))