Sumproduct and lookup help from other worksheets and multiple data within the secondary worksheet

tacokisses

New Member
Joined
Oct 26, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Link below.
Looking to figure out how to do a sumproduct (if that is the right formula) for a number of data onto one spreadshet. Looking on "tips Payroll" for a named individual to find the sum of all tips in "tips calc week one" under their name in each department. For instance, in "tips payroll" i Want name "Mark (eg) to give me his total tips from Bar, Cafe and Bottleshop departments from the worksheet "Tips Calc Week One" from the
Total column. Is this possible? I have added Test to test it but I cannot find a formula that works. Again, link below to download and check out the spreadsheet in excel. Or see it in Google Sheets. let me know. Thanks.

Tips Spreadsheet

1635303896179.png

1635303910714.png



Tips Spreadsheet
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Your formula:
sumproduct(P5:P14,p19: p29,... at least 1 range size is different ( first is 10, next is 11,...), while it require all be same size.

Try with common Sumif:

Code:
=SUMIF('Tip Calc Week One'!A1:A1000,A2,'Tip Calc Week One'!P1:P1000)
 
Upvote 0
Hi & welcome to MrExcel.
Another option
Excel Formula:
=SUM(FILTER('Tips Calc Week One'!P5:P43,'Tips Calc Week One'!A5:A43=A2))
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top