Excel problem. Please help.?

Question

I've got a spreadsheet, for my income and expenses. How do I calculate the number of, say, Fridays in each month. I currently have a list in a seperate file that I'm accessing with the vlookup function. It works fine, but is there an easier way?

Answer 1

- have a column for date
- then have another colum to return the number of day corresponding to the day in a week * =weekday(date)
- column for income or expenses
- use sumif(weekdaycolumn, 6, incomecolumn)

or
- have a column for date
- then have another colum to return the number of day corresponding to the day in a week * =text(date,"ddd")
- column for income or expenses
- use sumif(weekdaycolumn, "fri", incomecolumn)

Answer 2

You dont need a vlookup function to calculate how many fridays. You need a COUNTIF function, i.e. you count a range of cells and it brings up the number if it meets the criteria, in this case Friday. So, assuming you have a range of cells A1:A50, the function would be =countif(A1:A50,"Friday"). If the range of cells is A1:G50, =countif(A1:G50,"Friday") You probably know that rather than type the cell references in, you highlight the actual range so the cell refs go in automatically. Don't forget the comma or most importantly put Friday in inverted commas.

Answer 3

Use the function button located in the insert tab select date and time from the category change the function name to day to day and then select every column that has Friday in it, it sounds complicated but once it's set up you can just add the next Friday to sum function

Answer 4

It would take quite an elaborate formula to do it correct, thus using a vlookup is best. You're doing it exactly the easiest way.

Answer 5

No