Anyone good with Excel...

[Dall]

OK so not Mir related but thought someone maybe able to help...

At the mo I have a command:

=COUNTA('sheet1'!b3:b101)

which counts everytime an entry is made to one of the lines B3 to B101, what I would ideally like it to do is to only count if it reads FRI in that column, I know it should be easy but hoping one of you clever people would stop me having to hunt around and find the correct command/syntax...

Ta...

[ecalos]

surely countif...

[Dall]

That's the puppy, now sorted...

Many thanks that makes it look a lot, lot better

[ecalos]

lol np, i think i love excel

[Dall]

Next challenge for ya then

So command one:

=COUNTIF('Cust Details'!C3:C101,"SATURDAY")

Works great...

Counts the number of Saturdays that appear in that range, the next obstacle is to use countif with another rule.

So,

=COUNTIF('Cust Details'!B3:B101,"DJ")

BUT, I only want it to count matching criteria IF =COUNTIF('Cust Details'!C3:C101,"FRIDAY") is correct.

Now I'm guessing I need an IF Statement in there but having fun trying to get the syntax correct, any clues


This is for a legitamte reason btw, we are holding a VIP event where I will be fielding all the calls and making appointments for my sales team. I have one one sheet that logs all the details and am trying to show on the relevant pages (FRIDAY, SATURDAY & SUNDAY) how many appointments each of the sale team have.

Head Office will be reviewing the appointments so want it to look as tidy as possible

[Dall]

No matter, got it sorted

{=SUM(('Cust Details'!B7:B105="SG")*('Cust Details'!C7:C105="FRIDAY"))}

Sorted

[ecalos]

or imbed another If in there, nesting ftw :P

[Dall]

KK I guess one thing always leads to anther but this really is the last one...

I have four sheets in the workbook:

Friday Appt, Saturday Appt, Sunday Appt, Cust Details.

The three appt sheets are mainly for display and to ensure all sales guys have a similar number of appointments so also has a basic display with times down the left, names accross the top which can be printed on the day and given to the door staff.

The Cust Details sheet logs everything, Salesman, Day, Time, Cust Name etc (there the one Im interested in), what I would like for the workbook to do is when I enter the details ion the customer details screen eg Mr Smith to see salesman DJ at 10am on Friday for the cell on the Friday Appt sheet at 10am to be auto populated with Mr Smiths name, any ideas ???

[ecalos]

Sounds like you could probably use a VLookup to populate the relevant cells, may have to throw an ISBLANK negative check in but should be fairly simple. if you know how to set ranges etc then VLookups are piss to use.

If the Cust Details sheet is in exactly the same format as the other three except for the days etc then is is quite straightforward.

The trick I have found with Excel is not to try and over complicate a forumula, just dumb it down by adding an extra column to indicate a certain criteria and use this in further formulae.

Also depends if you are on 2003 or 2007 tbh, am sure you can do something sexy with Conditional Formatting which would help you too in 2007.

Need sleep now but let me know how you get on :P

[ecalos]

i actually thought about this when i was going to sleep lol. what i said above is bollox tbh. have various thoughts how to do this but involve multiple IF and Vlookup processes.

on cust details add an extra column for when you enter an appt. ie Monday 10 am etc etc then in the new column, IF this to return M10, this can then be used from your monday sheet for the same cell (in a new column) to return the details in the cust details for M10 - if that makes sense lol. you can always hide these columns once formulae set up.