Excel Formula help

[Springal]

Hi,

Seeing as the other thread got a good response thought I'd try with a formula I am totally stumped with!

I need to add up everything in column D, i.e (D1: D85), and put it in cell C85 but need to exclude anything in D1: D85, if there is a 'Y' in the corresponding cell in A, i.e don't include D5, if there is a Y in A5.

Appreciate any help, happy to take a PM to explain more if needed

Cheers

 

[PlayToe]

=sumif(A1:A85,<>"Y",D1: D85)

 

[leigull]

The only way i can think of doing it is long winded and Messi, sorry, messy.

A whole new column of IF statements along the lines of if cell in column A has a Y in it, return 0, if not return the value of the cell in D. Then a sum total at the bottom of that column. I.e. =if(A1="y",0,D1)

[MENTION=5200]Buzzer[/MENTION] might be able to come to the rescue again though!

 

[clapham_gull]

=sumif(A1:A85,<>"Y",D1: D85)

Perfect answer.

Personally (for visibility) I'd create new column with the only the numbers I wanted added.

So =if( A1="Y", D1,"")

Then sum those up.

Probably comes from having to add things up that aren't "numeric" - like video timecodes. I'm forced to create a numeric value in a another column.

 

[Muhammad - I’m hard - Bruce Lee]

dunno why i open these threads

 

[leigull]

I'll shut up then

 

[Seagull27]

Sorry, i don't know the answer.

But the NSC automatic smileys made me laugh

 

[clapham_gull]

I use VLOOKUP at least 5 times a week. I do love a VLOOKUP up, especially when combined with an iserror and an immediate if.

 

[PlayToe]

Should do the trick

 

[Cheshire Cat]

Personally I would use a slide rule

 

[Woodchip]

Surely it would be a countif statement given the original requirements. A sum formula would ignore of #ref any text in the array so wouldn't be of benefit.

EDIT: Scrub that. Just re-read the original question

 

[Cheshire Cat]

I do love a VLOOKUP up, especially when combined with an iserror and an immediate if.

Pervert

 

[clapham_gull]

Has anyone actually used an HLOOKUP ? I've never found a use for it/

 

[Woodchip]

Has anyone actually used an HLOOKUP ? I've never found a use for it/

I never have. I would presume it's if you have two horizontal lists, but who works in horizontal lists?

INDEX I thing it highly underrated.

 

[South Stand Bonfire]

I recall having an A level maths question in 1984 at Newman and had to give it miss as I ddn't understand that question either! Good luck with it...

Hi,

Seeing as the other thread got a good response thought I'd try with a formula I am totally stumped with!

I need to add up everything in column D, i.e (D1: D85), and put it in cell C85 but need to exclude anything in D1: D85, if there is a 'Y' in the corresponding cell in A, i.e don't include D5, if there is a Y in A5.

Appreciate any help, happy to take a PM to explain more if needed

Cheers

 

[clapham_gull]

I never have. I would presume it's if you have two horizontal lists, but who works in horizontal lists?

INDEX I thing it highly underrated.

I'm not so sure to be honest. I know someone who absolutely raved about it, but I showed him VLOOKUP and he never went back.

The huge whole in Excel is the lack of regular expressions which aren't supported natively.

Another things I find incredibly useful is data connections, just to CSV files. F*cking magic. Especially when they automatically fill in formulas you need to add to to the data.

 

[Buzzer]

Hi,

Seeing as the other thread got a good response thought I'd try with a formula I am totally stumped with!

I need to add up everything in column D, i.e (D1: D85), and put it in cell C85 but need to exclude anything in D1: D85, if there is a 'Y' in the corresponding cell in A, i.e don't include D5, if there is a Y in A5.

Appreciate any help, happy to take a PM to explain more if needed

Cheers

SUMPRODUCT formula is your best bet.

I don't have my laptop with Excel here at home but off the top off my head try this = SUMPRODUCT(D1: D85*(A1:A85<>"Y"))

It's essentially a multiple SUMIF.


Edit - I've put a space in after D1: because this forum thinks it's a smiley otherwise. I'm not sure if this works but can check tomorrow.

 

[Buzzer]

Actually, the sumif works better when there is just one variable such as here but for multiple sumifs you can really go to town with SUMPRODUCT and it's easy to read. You just do it in the format:

SUMPRODUCT(area you want to sum * (condition 1) * (condition 2) *.....) happy to give examples.

 

[Springal]

SUMPRODUCT formula is your best bet.

I don't have my laptop with Excel here at home but off the top off my head try this = SUMPRODUCT(D1: D85*(A1:A85<>"Y"))

It's essentially a multiple SUMIF.


Edit - I've put a space in after D1: because this forum thinks it's a smiley otherwise. I'm not sure if this works but can check tomorrow.

You are a star - thank you! Quick look looks ok, I'll hack around with it tomorrow!

Cheers all.... Anyone got any pics of Rachel Riley now ?

 

[clapham_gull]

SUMPRODUCT formula is your best bet.

I don't have my laptop with Excel here at home but off the top off my head try this = SUMPRODUCT(D1: D85*(A1:A85<>"Y"))

It's essentially a multiple SUMIF.


Edit - I've put a space in after D1: because this forum thinks it's a smiley otherwise. I'm not sure if this works but can check tomorrow.

That's very interesting. Multiplying an array against a boolean value, a True being "cast" as 1. I never considered that until I saw a similar example the other day.

got me thinking... cheers.