Excel

scratchnsurf · January 29, 2017

Any Excel wizards out there?

If so, I'd appreciate a PM to help me figure something out.

I've already tried copious amounts of sawdust.

ilikecheese · January 29, 2017

Surely we can solve this in public.

Whatcha trying to do?

scratchnsurf · January 29, 2017

ilikecheese said:
Surely we can solve this in public.

Whatcha trying to do?

I have 3 columns of numerical data. For this data, I have a formula that will 'read' the values that are in those columns and then total how many cells have a particular value in one or more of those columns. This works fine. What I want is to only select certain rows in those columns for the formula to be applied to. This selection is based on a value in another column.

roydo · January 29, 2017

I dont think I will contribute much in the way of help.

Turn it off and on though.

Philw · January 29, 2017

scratchnsurf said:
I have 3 columns of numerical data. For this data, I have a formula that will 'read' the values that are in those columns and then total how many cells have a particular value in one or more of those columns. This works fine. What I want is to only select certain rows in those columns for the formula to be applied to. This selection is based on a value in another column.

Can you use a vlookup?

Seathestars · January 29, 2017

Ctrl-alt-delete

Not sure what I'm saying but I find these words a little erotic.

SerenityNigh · January 29, 2017

Can you solve this using a series of IF statements?

scratchnsurf · January 29, 2017

Philw said:
Can you use a vlookup?

I did start looking into that, but I'm not sure it does what I want. Doesn't it just look at a set of data and indicate whether certain values are present?

Dario Terracotta · January 29, 2017

I'm a copy and paste the formulas type of guy myselflol

scratchnsurf · January 29, 2017

SerenityNigh said:
Can you solve this using a series of IF statements?

I have spent some time trying to do just that without success.

The bit I have that works is =SUM(IF((J2:J30<=2.75)+(K2:K30<=2.75)+(L2:L30<=2.75),1,0))

I now want to only apply that formula to the rows in the range that have a particular value in another column.

Philw · January 29, 2017

scratchnsurf said:
I did start looking into that, but I'm not sure it does what I want. Doesn't it just look at a set of data and indicate whether certain values are present?

My memory is a little vague on it, cos I'm relatively new to that kind of thing, and only used it once, but I think you can compare the contents of a cell to the vlookup value and then it will insert the contents of the corresponding cell into your table. Something like that at least.

https://exceljet.net/excel-functions/excel-vlookup-function

tmgc · January 29, 2017

scratchnsurf said:
I have spent some time trying to do just that without success.

The bit I have that works is =SUM(IF((J2:J30<=2.75)+(K2:K30<=2.75)+(L2:L30<=2.75),1,0))

I now want to only apply that formula to the rows in the range that have a particular value in another column.

You should said that straight away mate, sounds like your starter motors gone.

ilikecheese · January 29, 2017

scratchnsurf said:
I have spent some time trying to do just that without success.

The bit I have that works is =SUM(IF((J2:J30<=2.75)+(K2:K30<=2.75)+(L2:L30<=2.75),1,0))

I now want to only apply that formula to the rows in the range that have a particular value in another column.

Manipulating your data to create #AlternativeFacts...you make me sick.

Amw79 · January 29, 2017

scratchnsurf said:
I have spent some time trying to do just that without success.

The bit I have that works is =SUM(IF((J2:J30<=2.75)+(K2:K30<=2.75)+(L2:L30<=2.75),1,0))

I now want to only apply that formula to the rows in the range that have a particular value in another column.

If I'm understanding you correctly, you could do with another IF statement, before your formula

So if you're saying to only apply the formula if (for example) a10 has the same value as B10

IF(A10=B10, SUM(IF((J2:J30<=2.75)+(K2:K30<=2.75)+(L2:L30<=2.75),1,0)), "Something else")

With the something else, being what you want if a10 <> b10

The Cowboy · January 29, 2017

scratchnsurf said:
I have spent some time trying to do just that without success.

The bit I have that works is =SUM(IF((J2:J30<=2.75)+(K2:K30<=2.75)+(L2:L30<=2.75),1,0))

I now want to only apply that formula to the rows in the range that have a particular value in another column.

Sounds like you either need to use the FIND function, or familiarise yourself with the INDEX and MATCH functions.

It's hard to help without being able to get hands on with the data.

Also, sometimes it helps to not try to shoehorn all your calculation into one formula. Don't be afraid to have some interim formula that calculate a column at a time, then consolidate in a further formula.