This formula will show the value of A1+B1 if cell c3 contains Car otherwise it will show nothing

Solved

Posted on 2012-09-03

Hello,

I'm doing some calculations in MS Excel, and I want to be able to include a cell reference to the cell above in an IF statement.

For example, let's say I only want to perform an operation IF a desired value exists in the cell directly above my if statement.

Say my if statement is in cell C4, =sumif([c3.value equals 'Car'])

Thanks

I'm doing some calculations in MS Excel, and I want to be able to include a cell reference to the cell above in an IF statement.

For example, let's say I only want to perform an operation IF a desired value exists in the cell directly above my if statement.

Say my if statement is in cell C4, =sumif([c3.value equals 'Car'])

Thanks

22 Comments

This formula will show the value of A1+B1 if cell c3 contains Car otherwise it will show nothing

Sorry, I should have specified that before.

Let me clarify:

I want to sum the values in D7:U7 IF and only IF the cells in the range D6:U6 = 'Car'

In other words, not all the values in D7:U7 will be added together, but only the ones that correspond to the above value of 'Car'.

=(((SUMIF($D$6:$W$6,$AJ$4,

(((SUMIF(D6:W6,AK4,D7:W7))

You will see that there are two parts to this formula. Where it says "(SUMIF($D$6:$W$6,$AJ$4,$D

"(SUMIF(D6:W6,AK4,D7:W7)" ....I want to subtract the lowest value in the range, BUT ONLY for ONE of the two parts of the formula....whichever is lower.

For example

boat car car boat boat car

10 5 7 3 1 8

when I calculate the average, I want to drop the lowest value, but only from the part of the formula that applies to that particular vessel.

So if "((SUMIF(D6:W6,AK4,D7:W7)"

I hope that makes sense.

Better if you can upload a sample file.

After that, I want to find the average for boat...and the average for car to be calculated (separately).

Furthermore, there are two percentage values in AJ4 and AK4. From the above averages, I want to calculate the AJ4 percentage of boat and the AK4 percentage of car.

=(((SUMIF($D$6:$W$6,$AJ$4,

(((SUMIF(D6:W6,AK4,D7:W7))

This is the formula I'm working with currently. You will notice that I have an error in that I'm subtracting 1 from the count of boat and car, when I really just want the entity with the lowest value to have 1 subtracted from its count.

Please see attached example

boat-car-example.xlsx

boat-car-example.xlsx

=(SUMIF($D$6:$W$6,AJ4,$D$7

This is the formula I'm working with currently. You will notice that I have an error in that I'm subtracting 1 from the count of boat and car, when I really just want the entity with the lowest value to have 1 subtracted from its count.

=(SUMIF($D$6:$W$6,AJ4,$D$7

Using your formula, the result of the figures in my sample file is: 43.75543

When I calculate manually, my total is: 48.15149

Can you give me values separately for

Sumif for car

Sumif for boat

countif for car

countif for boat

average for car

average for boat

AE7*0.4

=(SUMIF($D$6:$W$6,AJ4,$D$7

I put in a series of low values (with one or two high values) and I got an average in the 50's.

I feel like this is very close, but there might be a need for a small tweak somewhere.

I really appreciate your help so far.

If you look at the attached file, I'm only having a problem with row 9 for some reason.

Maybe it has a problem with too many zeros?

boat-car-example.xlsx

I tried clearing all contents/formats on the cell, but it's still off by a lot. Any idea as to why this is?

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

make top menus bigger font | 3 | 23 | |

How to customise Office 2016 font settings with a GPO | 3 | 25 | |

Printer Setup Pop-up when Opening Excel File | 4 | 9 | |

VBA excel copy code causing excel to crash | 3 | 0 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!