Summarizing hourly pay with two different hourly rates.

Copper Contributor

Summarizing hourly pay with two different hourly rates.

Hi excel community, my names Owen and I'm currently 20 and I am relatively new to excel. I use excel to calculate my projected paychecks as well as budgeting my personal finances. I work as a bartender/server and I ran into some trouble multiplying both positions hours by each differing hourly rate the positions have. Any help would be much appreciated as I am still learning the trickier details of excel.

An EX:

12 Serving hours

Rate: 5.5

----------> 570\$

42 Bartending hours

Rate 12

This is how I've formatted my shift data, I tried to putting what position to clarify which hourly to use using SUMIFs, but I still had some trouble putting it together. I also tried using an IF statement but I am having issues with multiplying within the statement. I do not understand IF statements at all really. Very confusing for me.

I am on a bi-weekly pay schedule so I have two areas for each work week—data purposes. Also I haven't duplicated week ones format, do not know if I need that clarification—and I'm pretty tired. In the highlighted cell, I am trying to write the formula to combine my serving and bartending hourly income.

My Questions:

Is this possible with how I have formatted my data?

Do I have to reformat my data?

[Bonus question] How could I create a formula to tell me the certain day I worked a double?

I used CountIF to figure out the amount of shift "#>0" | as well doubles being 100% of the time always being over 10 hours and never less than 10 hours "#>9" | I cannot create a function to spit out a certain date for the doubles I have worked though. Such as 3/02/24, 3/03/2024. Or "Monday Week 1" "Wednesday Week 2" Just a side note I have a separate area where I manually input my AM and PM shifts, I am not that advanced for that to be automated.

-A future CPA

3 Replies

Re: Summarizing hourly pay with two different hourly rates.

"Do I have to reformat my data?"

I would indeed recommend that. List all you hours in a tabular from by date. That makes it so much easier to summarize. Example attached.

Re: Summarizing hourly pay with two different hourly rates.

Thank you! I had a suspicion I was making it much harder on myself. I'll try that!

Re: Summarizing hourly pay with two different hourly rates.

@owen_gib By the way, to account for a double rate, just add another column called "Factor", for example, and include in in the pay calculation as a multiplier. See attached.