asked on

Place a SUM to a cell based on the TYPE and MONTH in Excel 2010

We are trying to find a formula where it will read a series of values (date, type & amount) and accumulate thjem by the Type/Month.

Date		Type	Amount
01/01/16	BANK	$20.00 
01/15/16	BANK	$100.00 
02/05/16	CAR	$75.00 
03/06/16	BANK	$150.00 
03/20/16	CAR	$300.00 
04/02/16	CAR	$95.00

Open in new window

So based on the transactions above, we are trying find the correct formula that would calculate and display:

Type	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
BANK     $120.00       $150.00
CAR            $75.00   $300.00 $95.00

Open in new window

The attached excel shows the formulas that have worked partially (SUMPRODUCT and SUMIF).

Please advice on a formula or combinations of formula that will give us the result expected.
SumByType-Month.xlsx

ASKER CERTIFIED SOLUTION

Yamaafg

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Subodh Tiwari (Neeraj)

Try this ...
In D10

=SUMPRODUCT((MONTH($C$3:$N$3)=MONTH(B10))*($B$4:$B$5=C10)*$C$4:$N$5)

Open in new window

and copy down.

Professor J

you can easily do this with pivot table and everytime the data changes all you need to click is a refresh.

please see the video i created and uploaded. unzip the file and it is mp4 video
EE.zip

SOLUTION

Rob Henson

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jana

ASKER

Though both Yamaafg and Rob Henson works, Yamaafg could be done in the same sheet. Henson when placing the formula and editing for the same sheet gave 0.