Question description:
This user has given permission to use the problem statement for this
blog.
I'm trying to write a formula that will return the value of the Net Invoice Amount ('Invoice Data'!D) if the Account IDs (Calculations!F, 'Invoice Data'!A) and Billing Periods (Calculations!G1:V1, Invoice Data!C) match.
Solved by E. H. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
15/08/2018 - 03:29
Hello, attaching the file now
Excelchat Expert
15/08/2018 - 03:29
Hi
Excelchat Expert
15/08/2018 - 03:30
Welcome to Got it Pro
User
15/08/2018 - 03:30
I'm going to have to drop this into a Google Doc to share. The file is to large
Excelchat Expert
15/08/2018 - 03:30
ok
Excelchat Expert
15/08/2018 - 03:30
Please share on spreadsheet on right
User
15/08/2018 - 03:31
https://drive.google.com/open?id=1959L7Y-u8_sOaTpa-2GKa7578S_BFI_f
User
15/08/2018 - 03:31
I'll try
Excelchat Expert
15/08/2018 - 03:31
No not like this
Excelchat Expert
15/08/2018 - 03:32
Please share on spreadsheet
User
15/08/2018 - 03:34
It crashed when I tried to drop it in the sheet
Excelchat Expert
15/08/2018 - 03:34
Just make a small sample file and share the data
Excelchat Expert
15/08/2018 - 03:34
don't make it a huge file
Excelchat Expert
15/08/2018 - 03:35
One we figure out the soution you can incorporate in actual sheet
User
15/08/2018 - 03:35
Good idea
User
15/08/2018 - 03:36
Sorry.. keeps crashing
User
15/08/2018 - 03:38
Here
[Uploaded an Excel file]
Excelchat Expert
15/08/2018 - 03:39
Yes tell now
Excelchat Expert
15/08/2018 - 03:39
What is the requirement here?
User
15/08/2018 - 03:40
I need a formula that will place the associated Invoice Amount if the Account ID and Dates match
User
15/08/2018 - 03:40
Invoice amount is on the Invoice Data sheet under column D
User
15/08/2018 - 03:41
The Date for the Invoice is C
User
15/08/2018 - 03:41
And the Account ID for the Invoice is A
Excelchat Expert
15/08/2018 - 03:41
See the formula on calculations sheet
User
15/08/2018 - 03:42
Yes, dropping it into the original now
Excelchat Expert
15/08/2018 - 03:43
That is how the formula would look like if it matches ID and month-year data
Excelchat Expert
15/08/2018 - 03:43
=SUMIFS('Invoice Data'!$D:$D,'Invoice Data'!$A:$A,$A3,'Invoice Data'!$C:$C,B$1)
User
15/08/2018 - 03:44
That should work... my only thought is, what if there were two invoices in a month? That's why I didn't go with the Sumif originally
User
15/08/2018 - 03:45
I guess it would error out if that were the case with an IF Function
User
15/08/2018 - 03:45
Correct?
Excelchat Expert
15/08/2018 - 03:46
this is the file as well....if there are two invoices in a month with same id then they will add up in the formula
[Uploaded an Excel file]
User
15/08/2018 - 03:47
Cool, thank you so much!
Excelchat Expert
15/08/2018 - 03:47
Welcome
Excelchat Expert
15/08/2018 - 03:47
Please do give good rating and reviews
Excelchat Expert
15/08/2018 - 03:47
It would help a lot
Excelchat Expert
15/08/2018 - 03:47
Have a great day
User
15/08/2018 - 03:47
Of course, all 5 stars!
Excelchat Expert
15/08/2018 - 03:47
Take Care
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
