Data Range Question.
 

I have created a series of data ranges. In column A I have the state
listed, and in column Q I have a field with data "P" or "V". These
will be entered by other people in my group.
I created data ranges so that on a seperate worksheet I could do a
countif formula to count the number of P's are in Alabama. or the
number of V's in Texas.

My problem is that the sheet that I am pulling the data from gets (and
needs to be) sorted by different users. (This is a shared document.)
Once the sheet is sorted, my data ranges are not longer where they are
supposed to be, and my calculations are wrong.

Is there another better way to set this up?

Data Range Question.
 

sorting shouldn't make any difference.

Try

=SUMPRODUCT(--(2:A200="Texas"),--(Q2:Q200="P"))

adjust the range to suit.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ChuckF" wrote in message
oups.com...
I have created a series of data ranges. In column A I have the state
listed, and in column Q I have a field with data "P" or "V". These
will be entered by other people in my group.
I created data ranges so that on a seperate worksheet I could do a
countif formula to count the number of P's are in Alabama. or the
number of V's in Texas.

My problem is that the sheet that I am pulling the data from gets (and
needs to be) sorted by different users. (This is a shared document.)
Once the sheet is sorted, my data ranges are not longer where they are
supposed to be, and my calculations are wrong.

Is there another better way to set this up?

Data Range Question.
 

Thanks Bob, I was just using a sum formula which is why the sorting
effected my results, sumproduct works great...thanks

Data Range Question.
 

Thanks Bob, I was just using a sum formula which is why the sorting
effected my results, sumproduct works great...thanks