Can I get a little help on an Excel formula?
 

Seems there must be a simpler way to do this than what I have so far. I don't want to do reference cells so it needs to be done in one cell.

Anyway... assume there is a column of cells A1 through A10. I simply want to SUM all the occurrences of "3" in those cells.

Then, to that total, add the product of another cell (lets call it A12). But here's the kicker, I only want to add the amount from A12 that exceeds 2.

So if the product of A12 =1 then I don't want to add anything to my new cell, but if it's 6 I want to add 4. That's what has me hung up, if I simply subtract 2 from that cell as static part of my equation I will often get negative numbers which will screw everything up.

Any help is appreciated, I'm just too tired tonight.

I don't have excel open so you might have to tweak syntax, but this should do it.

=SUMIF(A1:A10,"=3",A1:A10)+IF(A12>1,A12-2,0)

Excellent, looks like that works. Thanks!

I think the second term would be +max(A12-2,0)

So if A12 = 6, max (6-2,0) = max (4,0) = 4

If A12 = 1, max (1-2,0) = max (-1,0) = 0

I spoke too soon, looks like there is trouble in using SUMIF, I don't want to add all the "3s" together, rather count the number of occurrences.

As given if four of the cells contain that "3" it is totaling it to 12 when I want it to be 4. I tried changing it from SUMIF to COUNTIF but it doesn't like that...hmmm

Still no Excel in front of me, but play with COUNTIF. As a hack you could take the result of the SUMIF and divide by three, but it isn't the most elegant solution ;)

No sweat I got it, here was the solution (using the actual cells):

=COUNTIF(F4:AF42,2)+IF(F43>1,F43-2,0)

I sincerely appreciate the help, I normally have no problem with this stuff but I'm exhausted and about to go in for 8 hours more...

Cool! Glad it is working for you now.

If you sum all the 3's together, you can then divide that sum by 3 to get your count of the number of 3's, and go from there.

Whoops - I guess I'm a little late to the Excel party... :D

-Z

I've gone that route before but in this case it would add yet another calculation to this "single cell" string. I'm trying to keep it as clean as possible. I do appreciate the idea though.