* SPECIAL OFFER * JOIN THE MYEXCELONLINE ACADEMY & GET ACCESS TO 500+ EXCEL TUTORIALS ON: FORMULAS, MACROS, VBA, PIVOT TABLES, CHARTS, POWER QUERY, POWER PIVOT, ACCESS PLUS MORE: 👉 www.myexcelonline.com/academy-yt
💻Access 1,000+ Microsoft Excel video training tutorials covering Formulas, Macros, VBA, Pivot Tables, Power Query, Power Pivot, Power BI, Charts, Financial Modeling, Dashboards, Word, PowerPoint, Outlook, Access, OneNote, Teams & MORE! JOIN TODAY TO ADVANCE YOUR EXCEL SKILLS 👉 www.myexcelonline.com/107-42.html
Thanks John.. if you want to skip CSE, you can use SUMPRODUCT instead of SUM, as in: =SUMPRODUCT(VLOOKUP(G13,B13:D16,{2,3},0)) I noodled some other formulas to return the same answer as follows: =SUMPRODUCT((B13:B16=G13)*(C13:D16)) {=SUM(IF(B13:B16=G13,C13:D16))} - requires CSE.. could also write as: {=SUMPRODUCT(IF(B13:B16=G13,C13:D16))} - but still requires CSE because of IF =SUMPRODUCT(INDEX(C13:D16,MATCH(G13,B13:B16,0),)) =SUMIF(B13:B16,G13,C13:C16)+SUMIF(B13:B16,G13,D13:D16) Having some fun with formulas. Thanks for the inspiration to learn and create. Thumbs up!
Thanks a lot. Would you please help to sort out my problem? I have a work sheet where VLOOKUP working properly but when i add a cell value that contain sheet name, i got error. Sheen name added using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). No issue at all, adding sheet name manually in that cell. but adding above formula i got error.
HI. Thanks for the question. I think yuor first argument just needs to be a cell or a string. If that doesn't help and you would like to send your sheet to our consulting services, we could more specifically help. Please check out www.myexcelonline.com/microsoft-excel-consulting-services/
Hi thanks for sharing this. Is there any way to calculate the sum vertically? I mean in rows ➡ instead of columns ⬇? For example, In the Units column if have two records for Tablets, How to get a filtered sum for the total value with vlookup function? Please help
Hi, I am struggling to find a way to do something. on my table in one column there are job codes, another ha names and one last is work hours. one job code might have the same name more then once as people book their time on the same job code everyweek. How can I create a formula to matches all the rows with jobcode and name and give the sum of work hours.? I have been trying for a while and couldn't make that work.
Sure! If you just want to look up the values and put them all in one sheet, use XLOOKUP if you are using Office 365 or VLookup for earlier versions (our XLOOKUP video is here: studio.ruclips.net/user/videoaRo-bzKzTqM/edit?c=UCMaVSMuAqV5j9WRdUz9UQfw). However, if you want to group all your values together, you could create a Pivot Table (we have a video on that here: studio.ruclips.net/user/videoOcMBMLrJvRw/edit?c=UCMaVSMuAqV5j9WRdUz9UQfw). Hope that helps!
You could do a sum at the beginning or end of a row for that entire row. Type =SUM and select the row. If that didn't answer your question well enough, please check out our blog at www.myexcelonline.com/blog/. Thanks for watching!
* SPECIAL OFFER * JOIN THE MYEXCELONLINE ACADEMY & GET ACCESS TO 500+ EXCEL TUTORIALS ON: FORMULAS, MACROS, VBA, PIVOT TABLES, CHARTS, POWER QUERY, POWER PIVOT, ACCESS PLUS MORE: 👉 www.myexcelonline.com/academy-yt
💻Access 1,000+ Microsoft Excel video training tutorials covering Formulas, Macros, VBA, Pivot Tables, Power Query, Power Pivot, Power BI, Charts, Financial Modeling, Dashboards, Word, PowerPoint, Outlook, Access, OneNote, Teams & MORE! JOIN TODAY TO ADVANCE YOUR EXCEL SKILLS 👉 www.myexcelonline.com/107-42.html
Thanks John.. if you want to skip CSE, you can use SUMPRODUCT instead of SUM, as in: =SUMPRODUCT(VLOOKUP(G13,B13:D16,{2,3},0))
I noodled some other formulas to return the same answer as follows:
=SUMPRODUCT((B13:B16=G13)*(C13:D16))
{=SUM(IF(B13:B16=G13,C13:D16))} - requires CSE.. could also write as: {=SUMPRODUCT(IF(B13:B16=G13,C13:D16))} - but still requires CSE because of IF
=SUMPRODUCT(INDEX(C13:D16,MATCH(G13,B13:B16,0),))
=SUMIF(B13:B16,G13,C13:C16)+SUMIF(B13:B16,G13,D13:D16)
Having some fun with formulas. Thanks for the inspiration to learn and create. Thumbs up!
I absolutely LOVE the SUMPRODUCT formula and it can do so many wicked calculations! Thanks for sharing your Formula Wayne :)
Thanks a lot. Would you please help to sort out my problem? I have a work sheet where VLOOKUP working properly but when i add a cell value that contain sheet name, i got error. Sheen name added using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). No issue at all, adding sheet name manually in that cell. but adding above formula i got error.
HI. Thanks for the question. I think yuor first argument just needs to be a cell or a string. If that doesn't help and you would like to send your sheet to our consulting services, we could more specifically help. Please check out www.myexcelonline.com/microsoft-excel-consulting-services/
Hi thanks for sharing this. Is there any way to calculate the sum vertically? I mean in rows ➡ instead of columns ⬇? For example, In the Units column if have two records for Tablets, How to get a filtered sum for the total value with vlookup function? Please help
Hi. Please check our blog at www.myexcelonline.com/blog.
Can you share video link on this if you have , it will be more useful for us
Nice... explain sir
You are welcome and we are glad you enjoyed this VLOOKUP tutorial!
Hi, I am struggling to find a way to do something. on my table in one column there are job codes, another ha names and one last is work hours. one job code might have the same name more then once as people book their time on the same job code everyweek. How can I create a formula to matches all the rows with jobcode and name and give the sum of work hours.? I have been trying for a while and couldn't make that work.
Sure! If you just want to look up the values and put them all in one sheet, use XLOOKUP if you are using Office 365 or VLookup for earlier versions (our XLOOKUP video is here: studio.ruclips.net/user/videoaRo-bzKzTqM/edit?c=UCMaVSMuAqV5j9WRdUz9UQfw). However, if you want to group all your values together, you could create a Pivot Table (we have a video on that here: studio.ruclips.net/user/videoOcMBMLrJvRw/edit?c=UCMaVSMuAqV5j9WRdUz9UQfw). Hope that helps!
How to sum of multiple rows like coloumn
You could do a sum at the beginning or end of a row for that entire row. Type =SUM and select the row. If that didn't answer your question well enough, please check out our blog at www.myexcelonline.com/blog/. Thanks for watching!