Forum Discussion
Expense report help
This is kind of a tricky solution. It's a little tough to understand....but basically I'm using a SUMPRODUCT formula to get you the result you require.
=SUMPRODUCT(0+(D12:T12="PayPal"),0+ISODD(COLUMN(C12:S12)),C12:S12)
The ranges are offset to align the two arrays. So for the PayPal range I use D12:T12 but for the $$$ Range I use C12:S12. The reason this offset is necessary is in order to align the two ranges where the $$$ is adjacent to the Criteria.
Here's a simplified example of how the data is being evaluated...
0.00 Paypal
3.00 Credit
4.00 PayPal
5.00 PayPal
Then this evaluates the items that are = PayPal
0.00 True
3.00 Credit
4.00 True
5.00 True
Then we sum up the numbers $4 + $5 + $0 = $9
Here's a little more info on SumProduct for your reference:
https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/
https://exceljet.net/formula/sum-every-nth-column
Please see the attached example file for the solution.
Sorry that I explained so poorly, but despite it all you were very close. I've updated the test expense report to be a "dumb" example of what I'm looking for. Thanks so much!
I just realized my math is off on the column furthest right. Oops.
Dusty-
Try this attached file. It should do what you want. Hope this helps!Matt,
I really appreciate all the help. I'm very close now and definitely grasp the idea now. I'm having a conflict with my formula. The row that is filled in all the way across generates a sum in the correct column, but if you remove any one of the "tender" options it generates an error. In reality, each row will be one purchase and they'll place the amount in the correct why/what/where column, so each row only have one of the columns generated, but obviously all need to be available in the formula to suit wherever the purchase might put it. I attached my actual template, with all sensitive information removed so you can see what I'm running in to. I believe I need to "nest" my formulas, but I'm not sure I know how to put those together. I also think my false value of "" could be wiping the numbers.
This is kind of a tricky solution. It's a little tough to understand....but basically I'm using a SUMPRODUCT formula to get you the result you require.
=SUMPRODUCT(0+(D12:T12="PayPal"),0+ISODD(COLUMN(C12:S12)),C12:S12)
The ranges are offset to align the two arrays. So for the PayPal range I use D12:T12 but for the $$$ Range I use C12:S12. The reason this offset is necessary is in order to align the two ranges where the $$$ is adjacent to the Criteria.
Here's a simplified example of how the data is being evaluated...
0.00 Paypal
3.00 Credit
4.00 PayPal
5.00 PayPal
Then this evaluates the items that are = PayPal
0.00 True
3.00 Credit
4.00 True
5.00 True
Then we sum up the numbers $4 + $5 + $0 = $9
Here's a little more info on SumProduct for your reference:
https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/
https://exceljet.net/formula/sum-every-nth-column
Please see the attached example file for the solution.