Formula help

[sachavez]

I'm using the following formula to look up and sum cost information for Brakes and Air System:

=SUMPRODUCT(($B$63:$B$102="Brakes & Air System")*($C$63:$N$102))

The formula works well, but I am charting year / year comparison and need to modify the formula to sum Jan through whatever month is listed in cell $C$2.

My month header row is 62.

My thought is that if I change cell $C$2 to Feb, the forumla will only sum the numbers listed in Jan & Feb for Brakes & Air System.

Thanks in advance.

 

[yytsunamiyy]

Assuming you dates are in Col C as true dates rather than text: Input the month in C2 as a date formated as "mmm" rather than text. Then try:

=SUMPRODUCT(--($B$63:$B$102="Brakes & Air System"),--(Month($C$63:$N$102)=$C$2))

 

[VoG]

assuming dates in column A maybe

=SUMPRODUCT(--($B$63:$B$102="Brakes & Air System"),--($C$63:$N$102),--(MONTH(A63:A102)<=2))

 

[sachavez]

Assuming you dates are in Col C as true dates rather than text: Input the month in C2 as a date formated as "mmm" rather than text. Then try:

=SUMPRODUCT(--($B$63:$B$102="Brakes & Air System"),--(Month($C$63:$N$102)=$C$2))

My months are entered in as general (no specific format).

 

[sachavez]

Here's how the data is set up. The add-in only pulled in 2011 data; my 2010 data set is in range $C$63:$C:$102.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
10			Year	Air Brakes	Chassis	Doors	ICC Bumpers	Landing Legs	Nose	Reefer	Roof	Side	Tires
11			2011	$7,330	$7,123	$3,091	$706	$4,932	$2,229	$2,286	$10,354	$12,008	$29,327
12			2010	$41,453	$43,197	$17,473	$5,621	$36,315	$10,423	$23,456	$35,063	$68,136	$190,437
13			Change	$34,122	$36,073	$14,381	$4,915	$31,384	$8,194	$21,171	$24,709	$56,128	$161,110
14			% Change	465.5%	506.4%	465.2%	696.5%	636.4%	367.6%	926.2%	238.6%	467.4%	549.4%
15			% of total	8.8%	9.2%	3.7%	1.2%	7.7%	2.2%	5.0%	7.4%	14.4%	40.4%
16													$6,226	2011 MTD Tires only
17	2011 Data												$33,272	2010 MTD Tires only
18	Source	Category	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
19	JBH	BRAKES & AIR SYSTEM	$138
20	JBH	CHASSIS UNIQUE ITEMS	$1,050	$1,262
21	JBH	CLEANING
22	JBH	CONTAINER UNIQUE ITEMS	$147
23	JBH	DECALS, MARKINGS, LOGOS
24	JBH	DOORS & HARDWARE
25	JBH	ELECTRICAL, LIGHTS, REFLECTIVE TAPE		$27
26	JBH	GRID SECTION & CROSSMEMBERS
27	JBH	ICC BUMPER AREA	$470
28	JBH	INTERIOR
29	JBH	LANDING LEGS	$901	$33
30	JBH	MISC
31	JBH	NOSE	$636	$1,020
32	JBH	REFR
33	JBH	ROOF	$451	$2,566
34	JBH	SIDE	$719	$6,667
35	JBH	TANDEM
36	JBH	TAX CODES
37	JBH	TIRES & RIMS	$3,113	$3,113
Alliance

 

[yytsunamiyy]

this should work then (if your dates are in Column C):

=SUMPRODUCT(--($B$63:$B$102="Brakes & Air System"),--(Month($C$63:$N$102)<=Month($C$2)))

if your dates are for instance in Col A try:

=SUMPRODUCT(--($B$63:$B$102="Brakes & Air System"),--(Month($A$63:$A$102)<=Month($C$2)),($C$63:$N$102))

If your months are in the header Row 62 col C-N offhand I don't see a way this is going to fly.

EDIT: Crossed with your post of data layout. The headers in Rows 18 / 62 are entered as text I presume?

 

[sachavez]

Correct: EDIT: Crossed with your post of data layout. The headers in Rows 18 / 62 are entered as text I presume?

this should work then (if your dates are in Column C):

=SUMPRODUCT(--($B$63:$B$102="Brakes & Air System"),--(Month($C$63:$N$102)<=Month($C$2)))

if your dates are for instance in Col A try:

=SUMPRODUCT(--($B$63:$B$102="Brakes & Air System"),--(Month($A$63:$A$102)<=Month($C$2)),($C$63:$N$102))

If your months are in the header Row 62 col C-N offhand I don't see a way this is going to fly.

EDIT: Crossed with your post of data layout. The headers in Rows 18 / 62 are entered as text I presume?

 

[yytsunamiyy]

Try:

Excel Workbook
	B	C	D	E
2		Feb
3			air brakes
4		2010	4
5		2011	8
6
7
8
9
10		jan	feb	mar
11	air brakes	1	1
12	xxx	1	1
13	air brakes	1	1
14
15		jan	feb	mar
16	air brakes	2	2	2
17	xxx	2	2	2
18	air brakes	2	2	2
Tabelle1

Cell Formulas
Range	Formula
D4	=SUMPRODUCT(--(($B$11:$B$13)=$D$3)*(INDIRECT("R11C3:R13C"&2+MONTH($C$2),FALSE)))
D5	=SUMPRODUCT(--(($B$16:$B$18)=$D$3)*(INDIRECT("R16C3:R18C"&2+MONTH($C$2),FALSE)))

Adjust ranges to your ranges 19 - 58, 63 - 102

C2 has a real date (01.02.2010) formatted as "MMM"

 

[sachavez]

This looks like it will work!

Thank you very much for your time and effort. I truly appreciate it!