Excel

[scratchnsurf]

Isn't there a function countif which might work. I haven't got my laptop so can't check?

I needed to use Sum(IF as I wanted it to look for a value across 3 columns, but if it appeared in 1, 2 or 3 of those columns only count it once.

Might be sumif



https://www.techonthenet.com/excel/formulas/sumif.php

I looked at that, but the summing bit works fine in the formula I already have. I just want it to discount certain cells (well rows actually) depending on the value in another column.

 

[SerenityNigh]

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.

At first glance it looks like you have too few if statements, need to nest more in your formula.

Or learn to use these formulas that do the work for you, but I can't speak to them.

 

[Tubey]

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.

 

[Caolam]

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.

I've had a few glasses of wine tonight as I am off tomorrow, but I think I can see we're you are going wrong. You are working at the weekend.

 

[blupedro]

This works in open office calc, I believe it should also work in excel.

SUMPRODUCT(SIGN((J2:J30<2.75)+(K2:K30<2.75)+(L2:L30<2.75))*(F2:F30="Y"))

Excel help should have info on the functions used.

 

[scratchnsurf]

This works in open office calc, I believe it should also work in excel.

SUMPRODUCT(SIGN((J2:J30<2.75)+(K2:K30<2.75)+(L2:L30<2.75))*(F2:F30="Y"))

Excel help should have info on the functions used.

That's it. Thanks so much, this has been driving me mad. I will now go and see if I can work out why it works!

 

[The Cowboy]

That's it. Thanks so much, this has been driving me mad. I will now go and see if I can work out why it works!

That's the strange thing about SUMPRODUCT - it always seems to serve it's purpose, but no-one understands how it works!

 

[Yorkie]

Press "alt" and "F4" to bring up the shortcuts menu.

 

[The Cowboy]

Press "alt" and "F4" to bring up the shortcuts menu.

You mean person.

 

[Yorkie]

You mean person.

Save it first @scratchnsurf

 

[Boss_Blue]

Save it first @scratchnsurf

LOL

Bottled it.

 

[Sy-]

This works in open office calc, I believe it should also work in excel.

SUMPRODUCT(SIGN((J2:J30<2.75)+(K2:K30<2.75)+(L2:L30<2.75))*(F2:F30="Y"))

Excel help should have info on the functions used.

Been using Excel for as long as I can remember, but SIGN is a new one to me, thanks!

 

[JLW]

*goes back to 'transfer thread'

 

[Yorkie]

LOL

Bottled it.

It sounded quite important!