Got something to say or just want fewer pesky ads? Join us... 😊

Excel Formula help



Springal

Well-known member
Feb 12, 2005
24,712
GOSBTS
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 :lol:

Cheers
 






leigull

New member
Sep 26, 2010
3,810
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!
 
Last edited:


clapham_gull

Legacy Fan
Aug 20, 2003
25,800
=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.
 










clapham_gull

Legacy Fan
Aug 20, 2003
25,800
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.
 






Cheshire Cat

The most curious thing..
Personally I would use a slide rule
 


Woodchip

It's all about the bikes
Aug 28, 2004
14,460
Shaky Town, NZ
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

The most curious thing..








South Stand Bonfire

Who lit that match then?
NSC Patron
Jan 24, 2009
2,484
Shoreham-a-la-mer
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 :lol:

Cheers
 


clapham_gull

Legacy Fan
Aug 20, 2003
25,800
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

Languidly Clinical
Oct 1, 2006
26,121
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 :lol:

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.
 
Last edited:


Buzzer

Languidly Clinical
Oct 1, 2006
26,121
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

Well-known member
Feb 12, 2005
24,712
GOSBTS
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 ? :D
 


clapham_gull

Legacy Fan
Aug 20, 2003
25,800
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.
 


Albion and Premier League latest from Sky Sports


Top
Link Here