countif (unique entries) 2 criterias

[jw01]

Hello

Having some trouble deriving a formula that will
- look for account # for a county by name

there are multiple duplicates so i would only like unique entries for a county, i have attached a sample, can someone pls help!

im really out of ideas, bc countif only allows for one criteria to be searched and i use excel 03 so i cant use countifs!

i have highlighted in "red" the column that the formula should be

pls help and much appreciated!

[JBeaucaire]

You want to use the Excel 2007 function COUNTIFS() which allows multiple criteria to be used.

=COUNTIFS(CountRNG, Criteria1RNG, Criteria1, Criteria2RNG, Criteria2, etc....)

[jw01]

hello

appreciate the prompt response.

the issue is, if i am to send the file out to the branches, not all branches are upgraded to excel 07, so is there a possible formula in excel 03 that i can cut around with? pls and thx u!

[JBeaucaire]

Sending out Excel files is always a pain, usually unnecessary. PDF your files and send these flat reports to others for review. If you send out Excel files anyone can change anything and then blame you for bad data.... ugh.

[jw01]

yeah that would be great but unfortunately cant do that lol

is that a sumproduct rule that can be applied here? thx u

[JBeaucaire]

1) Add a column to your data to make a key concatenating each county name and the number of times that code has occurred so far.

2) Then you can sum by the county name added to the "-1" count to get the first instance of each combination.

Does this work for you?

[jw01]

JBeaucaire,

you are simply the best, genius!

thx you so much!!!!!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.