SUMIF based on several criteria.

[Mike]

OK here is what I want to do. I have three different values that I want to
sum. I have several criteria that I have to consider when I sum them.

A B C D Sum Total
89 90 105
100 100 110
75 0 85
75 65 0 Go
65 75 0 No/Go

OK so here is a sample set of values. I want to sum the values from left to
right. For the majority of my totals I will only need to do a straight
sum(A,B,C) no big deal. However from time to time as in the first example
where run into a qualification.
Problem 1. If any of the three values are under 90 and any of the other
values are over 100 I need to round the value over 100 down to 100.

Problem 2. If any of the the values are a 0 then I need to be able in the
same cell average the other 2 number and then add the three values together.

I have tried to use nested If statments, looked at SUMIFS and SUMIF but the
SUMIFs only will do it if I have a range not individual cells to sum.

Ideas?

Thanks in advance.

Mike

 

[Squeaky]

Hi Mike,

Assuming your data starts in A2, B2, C2, and runs down. In D2 (or any column
on row 2) paste:

=IF(AND(IF(A2=0,(B2+C2)/2,A2)>100,OR(B2<90,C2<90)),100,IF(A2=0,(B2+C2)/2,A2))+IF(AND(IF(B2=0,(C2+A2)/2,B2)>100,OR(C2<90,A2<90)),100,IF(B2=0,(C2+A2)/2,B2))+IF(AND(IF(C2=0,(A2+B2)/2,C2)>100,OR(A2<90,B2<90)),100,IF(C2=0,(A2+B2)/2,C2))

Then drag/copy it down as far as you need.

Cheers.

Squeaky

 

[Mike]

Squeaky.

Thanks for the post. The only issue that I have is how do I incorperate the
GO / NO GO into the overall statement. I was not clear when I wrote the post
so I apologize up front.

There were there problems that I had, you got two of the three. The third
is to incorperate a GO or NO GO in column D. How it works is if column C is
zero then I need it to look at column d and see if it is GO or NO GO. If it
is a GO I need for it to average A and B and add it to A and B for an overall
total. If it is a NO GO I need it to just add A and B. There are also times
where I will only have A or B for a number and will have to validate with D
as Go or No Go. If it is a Go then the total of either A or B time 3 will be
the final number in the block.

If this is confusing let me know and I will attempt to claify.

Thanks.

Mike

 

[Squeaky]

Hi Mike,

Yes, try to re-explain what you need. Here is what I understand: If any
entry is zero you need for the form to average the other two numbers,
replace the zero with that number, then add all three except if the zero is
in the C column, where it will look to column D for either GO or NOGO. If it
is GO then it will work as above, if NOGO then just add A and B, no averaging
in C.

"There are also times
where I will only have A or B for a number and will have to validate with D
as Go or No Go. If it is a Go then the total of either A or B time 3 will be
the final number in the block."

Not sure what the rest is you are trying to explain. Times 3?

 

[Mike]

Squealy,

In all cases add all three values together. If any value is zero and the
other two values are not, average the two and add it to the sum of the first
two. In all cases if C is zero, check D for Go/ No Go. if D is Go average
and sum, else sum the first two values. If all values are zero, check D for
G/NG and use the value in D.

Let me know if it helps or if you need more.

Thanks again.

Mike

 

[Squeaky]

Hi Mike,

Sorry, but I have been away. It looks like we are saying the same thing
except where all values are zero. If D says GO or NOGO, how do I use the
value in D?

Squeaky.

 

[Mike]

Squeaky,

The value in D is only used if the value in c is 0, or P, and is a test for
all the other columns.

Like a true or false. If C is P or zero then D there is a value in D of Go
or NoGo. If D is true (Go) then average the scores in A and B and place the
value in for C and total all scores. If not then the calculation for C
becomes 0 (zero) and is placed in for the value of C and the other scores are
totaled and placed in E.

Not sure if that helps.

Send me an email and I will send you a sheet that should help understand
this better.

(e-mail address removed)

Thanks.

Mike