Treat numbers above a certain value as that value when summing

Hello Forum, I discoverede this place today while trying to fix an excel database I inherited from the previous person in my position, who inherited it from the previous person, etc.
I've decided to start from scratch, since the old one was an unholy mix of datatypes organized both vertically and horizontally. It might've once worked in access, eight incarnations ago, but I have no experience in that program.

I'm in the process of setting up an overview of grant applications submitted for peer review. In this new version, each line represents one application, and the interesting columns are the ones detailing whether peer reviewers have been found, and how many. I have a simple SUM function in one column, showing the number of found reviewers per application, but since only two are needed, and there is a risk that more than two agree to do the job (we basically spam potential reviewers across the world), this number is occassionally 3 or - rarely - higher.

I want to be able to sum this particular column so that I have an updated tally of how many reviewers have been found, and - crucially - how many we still need to find. Any number greater than two in this column will give a misleading value of how much work is left to do.

SO

How can I make excel treat 0 as 0, 1 as 1, 2 as 2, and >2 as 2 when making the grand sum?

Try something like this:

=MIN(2,COUNTA(A1:J1))

where you might have reviewers' names in any of the cells A1 to J1.

Hope this helps.

Pete

Try

=SUMIF(A1:A10,"<2")+COUNTIF(A1:A10,">=2")*2

Thanks Jonmo1, worked a charm :D