Customer Accounting SUMMER

M

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
This is my raw data on sheet 1

ReferenceAccountDateValue
INV 218736A28517/12/201314,667.60
INV 218753B09517/12/2013136,840.20
INV 218778K06617/12/201317,990.00
INV 218803N24917/12/201348,780.00
INV 218824P16317/12/201335,040.00
INV 218916C02818/12/201345,220.00
INV 218917J03718/12/201345,143.00
INV 218926J00418/12/2013162,000.00
INV 218944B09518/12/2013307,400.00
INV 218997C03818/12/2013800,000.00
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <tbody> </tbody>

This is my sheet2 where I need the summary account totals expecting B095 to add up, what formula on cell A2 AND B2 that I will copy downwards, please answer considering data will more thus read range should be to infinity

AccountValue
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
i would setup that data into a table - so it will then automatically be included in a pivot table
all you need to do is hit refresh on the pivot table after new data added

Excel Workbook
AB
3Row LabelsSum of Value
4A28514,667.60
5B095444,240.20
6C02845,220.00
7J004162,000.00
8J03745,143.00
9K06617,990.00
10N24948,780.00
11P16335,040.00
12(blank)
13Grand Total813,080.80
Sheet4
 
Upvote 0
etaf said:
i would setup that data into a table - so it will then automatically be included in a pivot table
all you need to do is hit refresh on the pivot table after new data added

Sheet4

*AB
3Row LabelsSum of Value
4A28514,667.60
5B095444,240.20
6C02845,220.00
7J004162,000.00
8J03745,143.00
9K06617,990.00
10N24948,780.00
11P16335,040.00
12(blank)*
13Grand Total813,080.80

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 92px;"><col style="width: 89px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

I would be happier to get it through a formula , 2 reasons , I hate pivot tables because I don't know how to use them nor the person I am suppose to report is a low end user, he won't know what is a pivot table , it will create un necessary confusion. I am used to that SHEET1 SHEET2 tech
 
Upvote 0
put this in A2 of sheet 1
=IFERROR(INDEX(Sheet2!B2:B1000, MATCH(0, COUNTIF($A1:A$1, Sheet2!B2:B1000), 0)),"")
use
control+shift+enter

and in B2 put
=SUMIF(Sheet2!B2:B1000,Sheet1!A2,Sheet2!D2:D1000)

Excel Workbook
AB
1AccountValue
2A28514667.6
3B095444240.2
4K06617990
5N24948780
6P16335040
7C02845220
8J03745143
9J004162000
Sheet1
 
Upvote 0
You should really reconsider a Pivot Table, as it will be a lot more efficient, and would take about 2 minutes to set up. It will also be dynamic, since all you need to do is refresh the Pivot Table when you add new invoice data. If you're using Excel 2010+ then you can add a Slicer to be able to select the account instead of a Filter.

HTH,
 
Upvote 0
etaf said:
put this in A2 of sheet 1
=IFERROR(INDEX(Sheet2!B2:B1000, MATCH(0, COUNTIF($A1:A$1, Sheet2!B2:B1000), 0)),"")
use
control+shift+enter

and in B2 put
=SUMIF(Sheet2!B2:B1000,Sheet1!A2,Sheet2!D2:D1000)

Sheet1

*AB
1AccountValue
2A28514667.6
3B095444240.2
4K06617990
5N24948780
6P16335040
7C02845220
8J03745143
9J004162000

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A2{=IFERROR(INDEX(Sheet2!B2:B1000, MATCH(0, COUNTIF($A1:A$1, Sheet2!B2:B1000), 0)),"")}
B2=SUMIF(Sheet2!B2:B1000,Sheet1!A2,Sheet2!D2:D1000)

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Please check closer , result nil, at most times I get working results , please check by trying it practically
 
Upvote 0
Please check closer , result nil, at most times I get working results , please check by trying it practically
Click to expand...

what do you mean by
please check by trying it practically
Click to expand...

the example is from a spreadsheet where it worked practically ?

are you setting up as an array formula and not just using enter?
 
Upvote 0
makinmomb said:
Please check closer , result nil, at most times I get working results , please check by trying it practically
Click to expand...

Nice reply to someone who went out of their way to help you. :confused:

Did you ensure to enter the Index/Match formula as an Array formula?
 
Upvote 0
Smitty said:
Nice reply to someone who went out of their way to help you. :confused:

Did you ensure to enter the Index/Match formula as an Array formula?
Click to expand...

Almost there with the Formula

{=IFERROR(INDEX(Sheet2!B2:B1000, MATCH(0, COUNTIF($A1:A$1, Sheet2!B2:B1000), 0)),"

corrected this to

{=IFERROR(INDEX(Sheet1!B:B,MATCH(0,COUNTIF($A1:A$1, Sheet1!B:B), 0)),"")}


Can someone please finish off the formula on B2 , then I am fully sorted
 
Upvote 0
makinmomb said:
Almost there with the Formula

{=IFERROR(INDEX(Sheet2!B2:B1000, MATCH(0, COUNTIF($A1:A$1, Sheet2!B2:B1000), 0)),"

corrected this to

{=IFERROR(INDEX(Sheet1!B:B,MATCH(0,COUNTIF($A1:A$1, Sheet1!B:B), 0)),"")}


Can someone please finish off the formula on B2 , then I am fully sorted
Click to expand...

Yes I am sorted I realised there was only a small typo thus with these two I have now fully workable file, thank you ETAF

=IFERROR(INDEX(Sheet1!B:B,MATCH(0,COUNTIF($A1:A$1, Sheet1!B:B), 0)),"")

=SUMIF(Sheet1!B:B,A:A,Sheet1!D:D)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
G
  • Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
918
Godders199
G
A
  • Question
Add Sequence numbers Based on Dates
Replies
1
Views
2K
Jeffrey Mahoney
Jeffrey Mahoney
O
  • Question
Survey data to capture full month
Replies
5
Views
738
DanteAmor
DanteAmor
S
  • Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
853
AlanY
A

Forum statistics

Threads
1,214,826
Messages
6,121,797
Members
449,048
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top