Corrected some text regarding method I used.

Methodology

$I$1 = lower value of 0.3

$K$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B…]]>

Corrected some text regarding method I used.

Methodology

$I$1 = lower value of 0.3

$K$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 1 and A Jar 3 metal values if B Jar 2 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 2 and A Jar 3 metal values if B Jar 1 value is <0.3 AND >3 (outside range)

A Jar 3 metal value if B Jar 1 and B Jar 2 value is <0.3 AND >3 (outside range)

A Jar 2 metal value if B Jar 1 and B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 1 metal value if B Jar 2 and B Jar 3 value is <0.3 AND >3 (outside range)

Display "B Jars outside value guideline" if B Jar 1, B Jar 2 and B Jar 3 values is <0.3 AND >3 (outside range)

Code in the sample

Code

- =IF(AND(AND($H3>=$I$1,$H3<=$K$1),AND($I3>=$I$1,$I3<=$K$1),AND($J3>=$I$1,$J3<=$K$1)),MEDIAN($B3:$D3),IF(AND(AND($H3>=$I$1,$H3<=$K$1),AND($I3>=$I$1,$I3<=$K$1),OR($J3<$I$1,$J3>$K$1)),($B3+$C3)/(($B3<>0)+($C3<>0)),IF(AND(AND($H3>=$I$1,$H3<=$K$1),OR($I3<$I$1,$I3>$K$1),AND($J3>=$I$1,$J3<=$K$1)),($B3+$D3)/(($B3<>0)+($D3<>0)),IF(AND(OR($H3<$I$1,$H3>$K$1),AND($I3>=$I$1,$I3<=$K$1),AND($J3>=$I$1,$J3<=$K$1)),($C3+$D3)/(($C3<>0)+($D3<>0)),IF(AND(OR($H3<$I$1,$H3>$K$1),OR($I3<$I$1,$I3>$K$1),AND($J3>=$I$1,$J3<=$K$1)),$D3,IF(AND(OR($H3<$I$1,$H3>$K$1),AND($I3>=$I$1,$I3<=$K$1),OR($J3<$I$1,$J3>$K$1)),$C3,IF(AND(AND($H3>=$I$1,$H3<=$K$1),OR($I3<$I$1,$I3>$K$1),OR($J3<$I$1,$J3>$K$1)),$B3,IF(AND(OR($H3<$I$1,$H3>$K$1),OR($I3<$I$1,$I3>$K$1),OR($J3<$I$1,$J3>$K$1)),"B Jars outside value guideline","Problem"))))))))

146, 39 or something else.]]>

What does =CODE(MID(A1,6,1)) return?

146, 39 or something else.

]]>I think you might need SUMIFS]]>

I think you might need SUMIFS

]]>Absolutely bang on Haseeb - appreciate that so much.]]>

Hello Freddy,

Try:

=SUMIFS(E$4:E$6,C$4:C$6,"<="&H4,D$4:D$6,">="&H4)

Absolutely bang on Haseeb - appreciate that so much.

]]>I still only want to sum once, as long as I have some of the info there.

New example added.]]>

I still only want to sum once, as long as I have some of the info there.

New example added.

Return a sum of the row if the cells in column A&C in tab A = those in a&C in tab b

Tab b will contain text and blank cells so they need to be ignored

Whilst the values will equal the same initially i will be overwriting the…]]>

Return a sum of the row if the cells in column A&C in tab A = those in a&C in tab b

Tab b will contain text and blank cells so they need to be ignored

Whilst the values will equal the same initially i will be overwriting the sum data in tab b with text, therefore the value will change and i want to report that value to the corresponding row based on the match described above.

i'm guessing an index/match but tried various and cannot get it to work.

]]>May need CSE entry depending on your version of Excel.]]>

=MEDIAN(IF(($A$2:A$360=I2)*($B$2:B$360=J2)*($C$2:C$360=K2),$F$2:F$360))

May need CSE entry depending on your version of Excel.

]]>Thanks Sobi]]>

i can only come up with the attached solution,had to insert some columns, thr might be a way to get the desired answer without inserting anything, but i do not know that

Thanks Sobi

]]>