Can I get a little help on an Excel formula?

lendaddy · 10-04-2013, 06:19 PM

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.

dtw · 10-04-2013, 06:29 PM

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)

lendaddy · 10-04-2013, 06:34 PM

Excellent, looks like that works. Thanks!

jyl · jyl's Garage

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

lendaddy · 10-04-2013, 07:00 PM

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

dtw · 10-04-2013, 07:09 PM

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

lendaddy · 10-04-2013, 07:10 PM

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...

dtw · 10-04-2013, 07:11 PM

Cool! Glad it is working for you now.

Z-man · Z-man's Garage

Quote:

Originally Posted by lendaddy

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

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...

-Z

lendaddy · 10-04-2013, 07:15 PM

Quote:

Originally Posted by Z-man

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.

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.