Excel Help

[Magic Johnson]

Ok, been asked by a user if he can get a formula to add up all the 1's next to a specific date and put the number beside the specific date at the bottom. I have no idea as Excel is my absolute weak spot when it comes to support. Any help would be greatly appreciated. Thanks.

 

[The Boosh!]

=sum(gWHATEVER:gWhatever)

 

[Magic Johnson]

Problem is the dates are mixed up so that isn't possible. Also the dates could change.

 

[The Boosh!]

Mark cup is good with excel. Have pm'd him no end of times asking for help.

 

[Magic Johnson]

He's not online.

Would it be something like this?

if F=2011 then add 1 to cell G245

Is that possible in excel?

 

[Sarah]

you will prob need a 'sumif' formula

Click on the little Fx thing at the top it will bring up a wizard

 

[Magic Johnson]

That seems about right Sarah. How do I get it to add 1 when it comes across a 2011 though?

Rage = F1:F241
Criteria = 2011
Sum_Range = blank

 

[bobby_the_one]

i would use VLOOKUP, once a range is defined the formulae will look for a spefic date, you can then do what you please with the value.

 

[Magic Johnson]

Can you be more specific with that mate? Cheers.

 

[Sarah]

vlookup wouldnt work, as it looks only whats in the cell and gets easily confused

its doesnt add up iirc

 

[ChrisPearson]

You need an IF statement not sure exactly how to do it ill have a think

 

[Sarah]

im guess it will be

=if(e:e,<1/1/10,>31/12/10)

 

[ChrisPearson]

=IF(F1:f12>"2010",Sum(G1:G12))

this isnt right but you get the idea

 

[Ronnie R]

The "Sumif=" formula is...

=+SUM

 

[JoedotwasT5phy]

Hang on, do you want to just put a "1" in the cell everytime a date is entered? then add those at the end?

 

[Magic Johnson]

Nope.

What I did was use the YEAR command to change the 1's to an actual year, then use the COUNTIF command to add whatever year it was up into the box next to the date at the bottom.

Thanks though guys.

 

[bobby_the_one]

This is a VLOOKUP formulae i use for something very similar, this wont work for you as you wont have a worksheet named budget nor will it have a user defined search area:

=IF(VLOOKUP(C417,Budget,2,FALSE)*F417<=0,"",VLOOKUP(C417,Budget,2,FALSE)*F417)

the 2 after the budget(worksheet name) instrucks the programme to search that colomn of data, so as long as the number relating to the date (C417 would be the location of the date) was in column 2 you wouldnt have a problem, you could also ask it to search columns 2&3 if you wanted.

I actually have this working now, unfortunately due to the nature of the content i can forward it on to you.

Go on google and search for an intermediate/professional excel guide.

 

[JoedotwasT5phy]

Nope.

What I did was use the YEAR command to change the 1's to an actual year, then use the COUNTIF command to add whatever year it was up into the box next to the date at the bottom.

Thanks though guys.

So you just want to add up the years?

 

[bobby_the_one]

So you just want to add up the years?

you ve lost me now

 

[Magic Johnson]

Lol, i've figured it out T5.