countif for multiple ranges and criteria

[Guest]

I have figured out how to add mutliple ranges and criteria using the countif
formula, but now I have a formula that is too long to fit in the cell formula
bar. Won't let me complete the formula.

Is there a way to combine two ranges in the same formula statement like:

=countif('input'!k2:k4+k40:k45,"1") ?

I have a column k 1 thru 560 or so that I have to count either a 1,2,or 3
in, but I can't count the whole column at the same time, I have to count
mutliple ranges. I figured out how to do that, but now my formula is too long?

Any help would be greatly apprecriated.
Thanks.

 

[Peo Sjoblom]

What is it between the ranges you are trying to count that makes it
impossible to use the whole range like K2:K560? You can name part of formula
so it doesn't get too big, see


http://www.cpearson.com/excel/nested.htm

http://www.cpearson.com/excel/named.htm

although Chip shows how to deal with the 7 nested limits it can be applied
to long formulas as well

 

[Guest]

I understand what you are saying but I don't think I will gain anything by
naming or nesting it?

Truth is, I have 5 possible criteria, and so far my highest range count is
10, but I have one other cell that may go above 10 range selections.
Here is what i am looking at: I need to count each DMOS (92F1O) with AUTH_GR
(E4), and if they have a 1 2 or 3 in the MOSQ cell. What you see here is a
filtered worksheet, and there are a whole bunch of different DMOS cell
inbetween each of these, and I have to keep the integrity of the sheet for
input.
Thanks for your help.

UIC PARA LINE POSN DMOS AUTH_GR MOSQ
WZN7A1 103 06 0030 92F1O E4 1
WZN7A2 104 05 0025 92F1O E4 3
WZN7A2 105 04 0060 92F1O E4 2
WZN7A2 105 04 0065 92F1O E4 1
WZN7A2 105 04 0070 92F1O E4 2
WZN7A2 105 04 0075 92F1O E4 3
WZN7A2 105 04 0080 92F1O E4 2
WZN7A2 105 04 0085 92F1O E4 3
WZN7A2 105 04 0090 92F1O E4 3
WZN7A2 105 04 0095 92F1O E4 3

 

[Peo Sjoblom]

If you are using autofilter you can use an equivalent of countif with some
tricks


=SUMPRODUCT(--($K$2:$K$560=1),(SUBTOTAL(3,OFFSET($K$2,ROW($K$2:$K$560)-MIN(ROW($K$2:$K$560)),,))))

will count 1s in the visible cell in K2:K560 when filtered

 

[Guest]

Wow, went way over my knowledge base on that one
I'm assuming reading through the all the other threads that a $ means the
whole column? I'm not familiar with OFFSET or MIN. I see where the ROW thing
comes into play, but I am just not sure how to write it? Sorry.
One thing I may not have explained correctly is that the cell I am putting
this formula in needs to "countif" if you will, specific cells that I
determine k2:K560 that have a 1,2,or 3, then my next cell on the worksheet
counts the same thing, but only the 1's and 2's, and so forth. It's really
screwy, I know.
I did figure out a work around though. All I have to do was RENAME the
worksheet title, and that brought my total formula length down, and I was
able to finsh it off

My formulas look like this:so you can see where I ran out of room the first
time.
Thanks for your help!!

=COUNTIF(UMR!K39:K40,"1")+COUNTIF(UMR!K39:K40,"2")+COUNTIF(UMR!K39:K40,"3")+COUNTIF(UMR!K57:K58,"1")+COUNTIF(UMR!K57:K58,"2")+COUNTIF(UMR!K57:K58,"3")+COUNTIF(UMR!K163:K164,"1")+COUNTIF(UMR!K163:K164,"2")+COUNTIF(UMR!K163:K164,"3")+COUNTIF(UMR!K269:K270,"1")+COUNTIF(UMR!K269:K270,"2")+COUNTIF(UMR!K269:K270,"3")

 

[Peo Sjoblom]

You can shorten that by using this instead

=SUM(COUNTIF(UMR!K39:K40,{"1";"2";"3"}),COUNTIF(UMR!K57:K58,{"1";"2";"3"}),COUNTIF(UMR!K163:K164,{"1";"2";"3"}),COUNTIF(UMR!K269:K270,{"1";"2";"3"}))

you can remove the quotations around the criteria numbers as well

=SUM(COUNTIF(UMR!K39:K40,{1;2;3}),COUNTIF(UMR!K57:K58,{1;2;3}),COUNTIF(UMR!K163:K164,{1;2;3}),COUNTIF(UMR!K269:K270,{1;2;3}))

 

[Guest]

Awesome!! I was hoping you were going to come back with something like that.
I figured it could be done, I just couldn't find out how to do it anywhere.

Are there any reference materials or websites you recommend? All I use is
the MS help online usually.

Thanks agian for all your help, this will make me look very intelligent with
my bosses.

 

[Peo Sjoblom]

Here are 4

http://www.contextures.com/tiptech.html

http://www.cpearson.com/excel/ExcelPages.htm

http://www.mvps.org/dmcritchie/excel/excel.htm

http://www.mcgimpsey.com/excel/index.html


there are many more but these 4 would cover about anything


My website has some interesting array formulas

http://nwexcelsolutions.com/page_3.htm

it hasn't been updated for a while though, hope I can get to that soon

Hope that helps

Peo