Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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.

    General answers:
    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. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    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
    Attached Files Attached Files

Posting Permissions

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