Excel tips and tricks

[Ghoulish Delight]

I'm using Excel a lot at work. All of our test results are being put into some huge, complex matrices. I've got some questions on how to get some things done, plus I just accidentally found a nifty feature.

First, the nifty feature. If you're in a column that's got some data, and some blank spaces in between the data, holding ctrl and pressing the up or down arrow will bring you to the next cell in that column that has data in it. Great for fast scrolling to your data points. I presume it works for left and right in rows as well.

Now, for my quesiton. Let's say you have two columns. Column A will be either blank or say "yes" for each row. Column B has numerical data for each row. Here's what I want to accomplish. For each row, if Column A="yes", add the data in column B to a total. I can write it in code, but I can't wrap my brain around doing it in excel without a long formula like this: IF(A0="yes", B0,0)+IF(A1="yes", B1,0)+IF(A2="yes",B2,0)+...etc on down the rows. There's got to be a better solution.

[Kevy Baby]

Damn you GD. Now I am not going to be able to get any work done because I will be trying to think of the solution for this.

Hopefully somebody will save my day and post a solution soon.

[scaeagles]

Ditto Kevy.

[scaeagles]

My solution, if possible.

I would add another column C. Column C has the following formula in each cell in the column -

=if (a1="yes",b1,"")

When copying the formula, of course the cell references change.

At the bottom of column C, a simple sum can be used for the range of data. This way, whenever the value of column A changes to or from yes, the data in column C is updated appropriately, and thus the total.

Of course, that only works if you can add column C.

[Ponine]

could you create a new column, even possbily hidden that would say
(If A=yes, then..)
and have it put the positive value in a new column, then add all the values in that column into a cell for your total?

[Ghoulish Delight]

Yeah, I've thought of that solution. That'll be my fall back, but I'm still hoping for a more elegant solution that doesn't require the extra column

[scaeagles]

Well, you just want everything, don't you?

[Moonliner]

Hummmm....

If we have

A B
1 yes 1
2 null 5
3 yes 2
4 null 7

Then try:

=SUMPRODUCT((B1:B4)*(A1:A4="yes"))


How's that?

Note: I just added "null" to help the spacing.

[Ghoulish Delight]

Give the man a see-gar, it works!

[scaeagles]

I bow to your Excel-ence, Moonliner.