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 !

 

[Special-K99]

Pivot table perhaps?
Or SUMIF or SUMPRODUCT?

Does it have to be VBA ?

 

[Aberdham]

not sure if pivot table would work since there isn't any indication of which type of invoice they belong to.

in case of SUMIF, do you then need separate formula for the respective type? not sure of how SUMPRODUCT works...?

 

[jtakw]

Hi,

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

Book1
	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
Sheet17

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

E2 formula copied down.

 

[Aberdham]

[TABLE="width: 488"]
<tbody>[TR]
[TD]Invoice No.
AP000424[/TD]
[TD] Amount
$ 550,00 [/TD]
[TD][/TD]
[TD]Category
AR[/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><colgroup><col><col><col span="2"></colgroup>[/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 !

 

[jtakw]

[TABLE="width: 488"]
<tbody>[TR]
[TD]Invoice No.
AP000424[/TD]
[TD] Amount
$ 550,00[/TD]
[TD][/TD]
[TD]Category
AR[/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 !

Change the 4 to 2

 

[Aberdham]

I did the same and change the 4 to 2, it says my formula contains an error. I am wondering if the Category type needs to be the exact match instead of just the 2 letters from the beginning ?

 

[Special-K99]

If it says "your formula contains an error" then youve typed it in wrong.
If the formula is returning an error such as #VALUE then column G contains text and should only contain numbers

 

[Aberdham]

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

 

[Special-K99]

@Aberdham: The formula is wrong.

There should be TWO closing brackets at the end of the formula not one.