Thread: I need help with a spreedsheet that shows my average tips.

1. I need help with a spreedsheet that shows my average tips.

I've set up a cash account in Quicken with the Categories of Payroll:tips:AM and Payroll:tips:PM. I create a report for those categories and export it to a text file. I open the text file in Excel with the columns of date (Column A), categories (Column B) and amount (Column C). I then format the date column using *Day, Month, Date and Year e.g. Wednesday, March 14, 2013.

I would like to display/filter the information by:

1) AM and PM, number of shifts and average overall

2) AM and PM by day of the week, number of shifts and average overall

3) By year and month.

So far so good until I try to COUNTIF "(=COUNTIF(H1:H200,"*Saturday*"))" using the name of the day. While Excel is displaying Wednesday, March 14, 2013 the data is 5/14/2013 so nothing is returned.

Second question can I nest COUNTIFs? One for the day (Column A) and the other for the shift (Column B) that return a total for amount (Column C). Would I do the same for the month and year?

Thanks,

Marc

2. Could you post an example file. You talk about cols A-C but have a formula using H. You ask about time of day and shifts but don't have it mentioned how the data is stored.

Excel dates are numbers, not text so you can not look for day of the week searching by text. You can convert to a text fieled to display day of the week or you can use IFs using a formula to look at day of the week. A countif will only look at 1 condition. you can use an array formula to look at multiple or if you have XL2007 or later you can use COUNTIFS which can do multiple criteria.

If you post an example and ask more specific questions we can provide some formulas.

Steve

3. Marc,

So far so good until I try to COUNTIF "(=COUNTIF(H1:H200,"*Saturday*"))" using the name of the day.
In cell H1 place the formula =WEEKDAY(A1) then copy down to cell H200
In cell H201 place the formula =COUNTIF(H1:H200,7) where 7 represents the 7th day of the week (Saturday), starting with Sunday as the 1st day of the week

Second question can I nest COUNTIFs? One for the day (Column A) and the other for the shift (Column B) that return a total for amount (Column C). Would I do the same for the month and year?
In cell K1 (or whatever column you choose) place the formula =IF(AND(WEEKDAY(A1)=7,B1="AM"),C1,0) then copy down
In cell K201 place the formula =SUM(K2:K200)

You will have the total amount for all the Saturdays AM shifts worked

Tips.png

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•