Log In
Register
Home
October 16, 2021
Offline
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..
VIP
Trusted Members
December 7, 2016
Offline
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
October 16, 2021
Offline
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. ✌
VIP
Trusted Members
December 7, 2016
Offline
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
October 16, 2021
Offline
Hi Anders..
I was close, but no quite clearly.. 
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. 
1 Guest(s)
