Excel Formula Question

jamesblue

I use Excel for my accounts, which works really well with all the formulas for working out estimate Year Ends, Jobs Per Month, Remote Support Jobs etc.

But I would like to work out a new stat, basically they amount of days worked within a month therefore how much I earn on average per day worked.

I have columns, which should make it easy

Date Month (Which is formula =IF(A168="","",TEXT(A168,"mmm"))

01/01/2010 Jan
01/01/2010 Jan
02/01/2010 Jan

Therefore I need to have a formula which can say I worked 2 days within Jan, does anyone know what that might be?

I thought there might have been something like UNIQUE therefore it would be 2, but I cannot find a formula like that.

Thanks

Lil306

Sorry, but confused by this.

Are you mainly trying to use a formula that will count up how many times the date occurs like in your example ?

jamesblue

Lil306 wrote: »

Sorry, but confused by this.

Are you mainly trying to use a formula that will count up how many times the date occurs like in your example ?

Basically because I put in the date against each job, I want to know how many days I worked in that month. You can see by the example, I did 3 jobs, but the formula wants to say I worked 2 days, i.e. in the example i worked on the 1st and 2nd.

I hope that makes more sense.


Thanks

The_Grandmaster

Can you not just write have tables with Jan, Feb, March... in one column and type in days worked in another?

or if by day: sum(B1:B31)
where in column A you have the dates
column B you have 1 for working and 0 for not working.

Lil306

jamesblue wrote: »

Basically because I put in the date against each job, I want to know how many days I worked in that month. You can see by the example, I did 3 jobs, but the formula wants to say I worked 2 days, i.e. in the example i worked on the 1st and 2nd.

I hope that makes more sense.


Thanks

Could you not put a column of a sort with the COUNTIF criteria?

EDIT - Ignore, but see latest replies

jamesblue

The_Grandmaster wrote: »

Can you not just write have tables with Jan, Feb, March... in one column and type in days worked in another?

or if by day: sum(B1:B31)
where in column A you have the dates
column B you have 1 for working and 0 for not working.

Not really because i would only fill in the date with the date i worked on a jobs. i can see formulas are really good with in Excel, and i am sure there is one to do it, but cannot see it.

i.e. i have lots like =SUMIF(Income!K4:K1530,"Apr",Income!F4:F1530) to work out income for Apr etc, but just trying to find one that picks up the unique records in the date field for the month, therefore that would say how many days i worked.


thanks

Lil306

Hi James,

Just a quick suggestion, would this work for you ?

=COUNTIF(A:A,A1)

Change as appropriate, column A is the date range you enter, and A1 is the first cell you want to check.

Basically if it says 1, that date only appears once, if it says 50 it means that date appears again 50 times in the sheet (so 50 days worked)?

jamesblue

Lil306 wrote: »

Could you not put a column of a sort with the COUNTIF criteria?

Using COUNTIF would put numbers against each entry to display how many times it appears in the sheet / column?

Or alternatively an IF with a COUNT command?
=IF(COUNT(A1=A:A),"Duplicate Found","No Duplicate") or something daft like that.... sorry going to check that formula, I know it's wrong it's just to remind me when I edit it

This really looks like it will do the trick, i did not know about the No Duplicate options etc. I will let you know, after some test.

Lil306

jamesblue wrote: »

This really looks like it will do the trick, i did not know about the No Duplicate options etc. I will let you know, after some test.

Shame the formula is wrong :P

I knew it was wrong it was just to remind me hence the edit, see my above reply for a simpler method though. I'm busy playing with excel now just to get the formula right.

jamesblue

Lil306 wrote: »

Shame the formula is wrong :P

I knew it was wrong it was just to remind me hence the edit, see my above reply for a simpler method though. I'm busy playing with excel now just to get the formula right.

Thanks, i can see it is not as easy as i thought, i will await your reply.

Thanks again

Lil306

Think this may work

=IF(COUNTIF(B:B,B2)>1,"More than 1 date","Only 1 day")

Basically, it checks the cell range B:B (dates entered being in column b) and looks to see how many times the entry from cell B2 appears in that range, the formula than says if that date appears >1 time, then "more than 1 day worked", otherwise if it appears only once then "only 1 day worked"

Does that help, formula can then be replicated down the sheet?

EDIT - Attached picture, probably easier to explain looking at it than typing it.