• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Really Struggling With This One|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Really Struggling With This One|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Really Struggling With This One
sp_PrintTopic sp_TopicIcon
Really Struggling With This One
Avatar
Lee Elliot
Member
Members
Level 0
Forum Posts: 8
Member Since:
October 16, 2021
sp_UserOfflineSmall Offline
1
October 16, 2021 - 2:41 am
sp_Permalink sp_Print

Hi all,

Hoping someone can help here.. I have literally tried everything I can think of to get an answer from excel properly..

so, the attached file (sorry i don't know how to remove more than one file, they are all the same)is a mock of what I am trying to do, here is the criteria;

take each matching Source.name

find the invoice value for each resource then

multiply this by the January/February/march etc dates

 

a live example would be;

FV001.xlsx contains 5 rows of data

I want to take Lacey Koch find her invoice value (331) and multiply it by the January value of 19 = 6289

THIS WORKS (you can see it on row 30 a quick formula I pulled together) - BUT

the remaining 4 rows are ignored, the end product should give me 15010 in one cell

 

to make a helper column I would be adding data that's not really needed to a document?

any ideas anyone? pretty please.. 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
October 16, 2021 - 7:39 am
sp_Permalink sp_Print

Hello,

Welcome to MOTH!

Thanks for the sample file, it makes it far easier to understand the issue and to find a solution.
I understand it that you want the invoice value to be summed for the rows having same source name, that part is easily achived with either SUMIF(-S) or SUMPRODUCT, but as you write that you want to find the invoice for Lacey Koch I don’t see why you then also want to add the invoice value from the others, this also raises the question which multiplier is to be used if we for example look at FV002.xlsx, where Trent has 16 but the others 20.

=SUMPRODUCT((Invoicing[[Invoice Value]:[Invoice Value]])*(Invoicing[[Source.Name]:[Source.Name]]=$A$31))

=SUMIFS(Invoicing[[Invoice Value]:[Invoice Value]];Invoicing[[Source.Name]:[Source.Name]];$A$31)

The above formulas will give the sum of invoice value for all rows having same source name as entered in cell A31. But now the issue of what to multiply with, how do you define that as you want the result in one cell? The highest number? Perhaps the lowest? Or each rows value multiplied with each rows multiplier and then summed?

For the latter scenario you can use following formula:

=SUMPRODUCT((Invoicing[[Invoice Value]:[Invoice Value]]*Invoicing[01/01/2021])*(Invoicing[[Source.Name]:[Source.Name]]=$A$31))

I hope this will give you some help moving forward.

Br,
Anders

Avatar
Lee Elliot
Member
Members
Level 0
Forum Posts: 8
Member Since:
October 16, 2021
sp_UserOfflineSmall Offline
3
October 16, 2021 - 11:15 pm
sp_Permalink sp_Print

Hi Anders. 

You are a legend.. The solution you provided at the bottom

=SUMPRODUCT((Invoicing[[Invoice Value]:[Invoice Value]]*Invoicing[01/01/2021])*(Invoicing[[Source.Name]:[Source.Name]]=$A$31))

Was spot on. Thanks so much for this.

Looking at the above though, are we able to build a string out the date? On the front sheet, which wasn't included, is a year cell. I thought I could apply something like this;

*Invoicing["01/01/"&Sheet1!A1])

Where a1 on sheet1 is a cell containing a year in this this case 2021 but if it changed to 2022/2023 etc, it would build accordingly and hunt the year and return the value.

Or due to data being contained on a table we are forced to reference the column name as an absolute? Currently I have had to create a helper table containing your formula for each month and use multiple nested IF statements to check the year and find the right data.

If you need me to send an up to date doc for you to refer to then I can draft something up. 

 

Again, thanks a lot for your help. ✌ 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
October 18, 2021 - 1:42 am
sp_Permalink sp_Print

Hello,

Yes, it can be done, you do need to wrap with INDIRECT for it to work.

=SUMPRODUCT((Invoicing[[Invoice Value]:[Invoice Value]]*INDIRECT("Invoicing[01/01/"&$A$1&"]"))*(Invoicing[[Source.Name]:[Source.Name]]=$A$31))

But in such case I think it is better to have the table column header like "Jan 1" where it will be the same regardless of what year you look at. Example below.

=SUMPRODUCT((Invoicing[[Invoice Value]:[Invoice Value]]*Invoicing[Jan 1])*(Invoicing[[Source.Name]:[Source.Name]]=$A$31))

Good luck and stay safe!

Br,
Anders

Avatar
Lee Elliot
Member
Members
Level 0
Forum Posts: 8
Member Since:
October 16, 2021
sp_UserOfflineSmall Offline
5
October 20, 2021 - 4:09 am
sp_Permalink sp_Print

Hi Anders.. 

I was close, but no quite clearly.. Laugh

I used the middle formula for the time being as the source files and power query would be a pain to update at this late stage but I will remember this. 

 

Thanks for all your help. Really appreciate it. 

Take care. Cool

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Riny van Eekelen, michael serna
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.