Anybody good at excel formulas?

Dano

Dano

Dano

Soldato
Joined
19 Dec 2006
Posts
10,337
Location
UK
Trying to work out how to do this in an easier manner...

=COUNTA(F4)*D4+COUNTA(F5)*D5+COUNTA(F6)*D6 and so on all the way down to row 45.

What I'm trying to do is add together the figures in column D but only where there is an entry (of any kind) in the corresponding monthly column so we get a total at the bottom of the monthly column.

Here's the sheet if you want to play: http://homepage.ntlworld.com/daniel.clarke17/arghhh.xls

I suck at this sort of stuff and have no idea how to simplify it due to a complete lack of knowledge and experience with excel, any help would be greatly appreciated :)
 
Ok, got this one sussed for you.

Insert a column between Jan and Feb. This new column should be 'F'
In F4 copy the following formula "=IF(G4>0,D4,0)"
This will now only display the figures from the Neg Rate column if there is a figure in the Feb column.
Copy the formula all the way down the column and total at the bottom.
You can then 'hide' column F so that these working are not visible (if you wish to) and carry the total from F26 to G26.

Done!
 
Last edited:
You need something like this:

(This is just a working forumla I have)

If the cells between F9 and F121 = to the value held in A5 then sum the column D9 to D121

=SUMIF('F9:F121,A5,D9: D121)

Does that help?
 
Last edited:
div0 said:
This does what you want:

=SUMIF(F4:F45, ">0", D4:D45)
Click to expand...

This works perfectly until it hits one of the columns with an 'x' in it, at which point it fails, now assuming (probably wrongly...) that the >0 section in the middle is checking for a value greater than zero can I change it to a test to check if the cell is empty?
 
Ok, after a little playing I discovered by accident that by using:

=SUMIF(F4:F45, "<>", D4:D45)

works, is there anything bad about it? Seems odd using a does not equal operator with nothing to compare it to, or does it be default compare with nothing, hence it working as I want?

Thanks all :D
 
Last edited:
Dano said:
This works perfectly until it hits one of the columns with an 'x' in it, at which point it fails, now assuming (probably wrongly...) that the >0 section in the middle is checking for a value greater than zero can I change it to a test to check if the cell is empty?
Click to expand...

Yeah, sorry I didn't know how you wanted the 'x' cells to be treated. I thought it would always be a number in those columns.

Change the formula to this, so it will sum for any non-blank cell:

=SUMIF(F4:F45, "<>", $D4:$D45)

/edit - I see you just beat me to it - yeah there should be nothing wrong with doing that :)
 
You must log in or register to reply here.
Back
Top Bottom