Category sum up

[Aberdham]

Hi Excel Gurus,

I am currently trying to sort out the total net Revenue within my Invoice workbook.

Here is how it Looks like :

I have 3 different types of invoices:

- AR-XXX
- AP-XXX
- MA-XXX

They are all mixed together. Now I would like to find out the sum of each catories.

could any of you please write a VBA code to resolve this issue?

Hope to hear from you soon !

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]invoice No.[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]AP000424[/TD]
[TD]

$550.00​

[/TD]
[TD][/TD]
[TD]AR[/TD]
[TD]

$646.50​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]AR000222[/TD]
[TD]

$646.50​

[/TD]
[TD][/TD]
[TD]AP[/TD]
[TD]

$12,550.00​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]AP000513[/TD]
[TD]

$12,000.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


e2=SUMIF($A$2:$A$4,D2&"*",$B$2:$B$4) copy down

 

[Aberdham]

=SUMPRODUCT((LEFT(A$2:A$152,2)=E2)*(B$2:B$152))

Here I tried again, still it says it contains an error...

I am wondering if the invoice number 15AP000028 has to be the complete match as the category (15AP)

 

[MARZIOTULLIO]

if your invoice are 15AP000028 16AR00029 formula in my post # 11 is

E2=SUMIF($A$2:$A$152,"*"&D2&"*",$B$2:$B$152) copy down

 

[Aberdham]

How come The formula in sheet one works and in sheet 2 it returns a 0 ?

 

[jtakw]

Hi,Here's a sample of how a SUMPRODUCT solution would be, same formula for all Invoice types:

	A	B	C	D	E
Invoice Amt	Category	Total
AR-123	AR
AP-234	AP
MA-345	MA
AR-456
AP-567
MA-678

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]$111.00
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]555
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]$222.00
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]777
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]$333.00
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]999
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"]$444.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"]$555.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"]$666.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet17​

Worksheet Formulas Cell <tbody> [TH="align: left"]Formula [/TH] [TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2 [/TH] [TD="align: left"]=SUMPRODUCT( (LEFT(A$2:A$7,2)=D2)*(B$2:B$7) )

<tbody>

</tbody>

[/TD]

</tbody>

E2 formula copied down.

[TABLE="width: 488"]
<tbody>[TR]
[TD]Invoice No.AP000424
[/TD]
[TD] Amount $ 550,00
[/TD]
[TD][/TD]
[TD]CategoryAR
[/TD]
[/TR]
[TR]
[TD]AR000222
[/TD]
[TD] $ 646,50
[/TD]
[TD][/TD]
[TD]AP
[/TD]
[/TR]
[TR]
[TD]AP000513
[/TD]
[TD] $ 12.000,00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
=sumproduct((left(D$1588:D$1673,4)=AH1588)*(G$1588:G$1673))I have tried the formula, how come it doesn't work? am i getting it wrong somehow? please let me know thanks !

I literally copy paste the formula... could you please check where it goes wrong ?

You couldn't have copied and pasted the formula, as mentioned above, change the 4 to 2, and as Special-K99 pointed out, you're missing an ending bracket.

 

[jtakw]

You've changed your Invoice No. structure, in OP and Post #5 , it's AR, AP, etc. followed by numbers, now it's ##AR #####, etc.

If it's Always 2 digits Before the 2 Alphabets, change the formula to:

Excel 2010
	A	B	C	D	E
1		Invoice Amt		Category	Total
2	12AR-123	$111.00		AR	555
3	34AP-234	$222.00		AP	777
4	56MA-345	$333.00		MA	999
5	12AR-456	$444.00
6	67AP-567	$555.00
7	89MA-678	$666.00
Sheet1

Cell Formulas
Range	Formula
E2	=SUMPRODUCT((MID(A$2:A$7,3,2)=D2)*(B$2:B$7))

 

[Aberdham]

ah that works out perfectly, Thanks a lot !

what If i have other invoices listing as MSN-16-0002-02-001D

How should i set the category and the respective formula?

Hope to hear from you soon!

 

[jtakw]

I suggest you use the formula posted by MARZIOTULLIO in Post #13 if your invoice numbers are Not uniformly structured, like below:

Excel 2010
	A	B	C	D	E
1		Invoice Amt		Category	Total
2	12AR-123	$111.00		AR	555
3	34AP-234	$222.00		AP	777
4	56MA-345	$333.00		MA	999
5	12AR-456	$444.00		MSN	888
6	67AP-567	$555.00		ABC	1111
7	89MA-678	$666.00
8	MSN-16-0002-02-001D	$888.00
9	1234ABC-0012345	$1,111.00
Sheet1

Cell Formulas
Range	Formula
E2	=SUMIF($A$2:$A$152,"*"&D2&"*",$B$2:$B$152)