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]

	A​	B​	C​	D​	E​
1​	invoice No.	Amount		Category
2​	AP000424	$550.00​		AR	$646.50​
3​	AR000222	$646.50​		AP	$12,550.00​
4​	AP000513	$12,000.00​

<tbody>
</tbody>

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
1		Invoice Amt		Category	Total
2	AR-123	$111.00		AR	555
3	AP-234	$222.00		AP	777
4	MA-345	$333.00		MA	999
5	AR-456	$444.00
6	AP-567	$555.00
7	MA-678	$666.00

<tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT((LEFT(A$2:A$7,2)=D2)*(B$2:B$7)) <tbody> </tbody>

<tbody>
</tbody>

E2 formula copied down.

Invoice No.AP000424	Amount $ 550,00		CategoryAR
AR000222	$ 646,50		AP
AP000513	$ 12.000,00

<tbody>
</tbody>

=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)