Multiple functions in one cell

[reid1201]

In a single cell, I want to sum a range of numbers, then if that sum meets a certain criterion, then the cell will produce a result. for example: in G4, I want to sum A4:F4, if that sum = 12, then I want it to show the date listed in cell G3.

Is this possible?

Steven

 

[azumi]

Pls provide the data table...

Thanks

 

[mikerickson]

How about
=IF(SUM(A4:F4)=12, G3, SUM(A4:F4))

 

[Benvolio]

How about
=IF(SUM(A4:F4)=12, G3, SUM(A4:F4))

To expand upon Mike's (correct) answer, if you wanted the cell to return a different value if the sum was not correct you just insert it into the end of the IF expression; so =IF(SUM(A4:F4)=12, G3, "Sum not correct") or =IF(SUM(A4:F4)=12, G3, "") for a blank cell, etc.

 

[reid1201]

ADVERTISEMENT

Thanks everyone. That did exactly what I wanted it to. Looking at it now it seems so straightforward. But when I was working on it last night, I just couldn't figure it out. I appreciate the help.

 

[mikerickson]

^^ sleep does that

Seriously, sleep is one of my most successful problem solving techniques, surpassed only by asking for help. Its not that I get good help, its that composing the question clears out the irrelevances and brings the issue at hand into focus.

 

[bluemjk]

ADVERTISEMENT

New to the forum. I have one for you guys. In one cell, I need the sum of three cells; however, if NA appears then it is zero. Then, those same three cells need to be multiplied by 100, divided by 3 and then divided by 10. I have been at it for about four hours. Talk about a waste of man-hours. Can anyone assist?

 

[mikerickson]

How about
=IFERROR(SUM(A1:A3)*10/3, 0)

If the three cells aren't next to each other, use syntax like SUM(B1, D5, E34)

 

[bluemjk]

Thanks Mikerickson. However, when I use this formula, it calculates either the sum or zero. This occurs even when I use the syntax referenced above. The cells with the data are F9 (100%), F12 (77.8%), F15 (#N/A) and the formula is entered in cell F18 (0.0). I would need to be able to have the formula calculate the average, reading N/A as 0.

The reason F15 is entered as n/a is to reflect no data reported for the month and I need this entry to suppress the data point in my line chart.

Hope I am making sense..

 

[mikerickson]

Oh, then
=(IFERROR(F9,0)+IFERROR(F12,0)+IFERROR(F15,0))*10/3