Only use data from certain headers/categories/groups

[wesleyterrill]

Hello, everyone!

Every day, I use several reports that look like the table below (I can export as csv or xlsx.) I need to be able to pull data only from certain headers - specifically all 1900 and 3900 groups. (In my actual data, that will include 1901, 1902, 1903, etc.) But I need to exclude the 2900 categories.

What would be the best way to go about this? If the formula were correct, it should add up the 1900 and 3900 totals: $575 (D4) + $95 (D8) + $450 (D17) + $350 (D20). Any help would be greatly appreciated!

	A	B	C	D
1	1905 - Doors
2		Invoice #1		$250
3		Invoice #2		$325
4			Total	$575
5	1945 - Windows
6		Invoice #3		$50
7		Invoice #4		$45
8			Total	$95
9	2905 - Maintenance
10		Invoice #5		$25
11			Total	$25
12	2975 - Repair
13		Invoice #6		$40
14			Total	$40
15	3905 - Construction
16		Invoice #7		$450
17			Total	$450
18	3925 - Equipment
19		Invoice #8		$350
20			Total	$350

<tbody>
</tbody>

 

[oldbrewer]

1905 - Doors				1905 - Doors	19
	Invoice #1		250	1905 - Doors	19
	Invoice #2		325	1905 - Doors	19
		Total	575	1905 - Doors	19
1945 - Windows				1945 - Windows	19
	Invoice #3		50	1945 - Windows	19
	Invoice #4		45	1945 - Windows	19
		Total	95	1945 - Windows	19
2905 - Maintenance				2905 - Maintenance	29
	Invoice #5		25	2905 - Maintenance	29
		Total	25	2905 - Maintenance	29
2975 - Repair				2975 - Repair	29
	Invoice #6		40	2975 - Repair	29
		Total	40	2975 - Repair	29
3905 - Construction				3905 - Construction	39
	Invoice #7		450	3905 - Construction	39
		Total	450	3905 - Construction	39
3925 - Equipment				3925 - Equipment	39
	Invoice #8		350	3925 - Equipment	39
		Total	350	3925 - Equipment	39
		1470
		formula giving 1470
		=SUMPRODUCT(($C$1:$C$20="Total")*($F$1:$F$20=19)*($D$1:$D$20))+SUMPRODUCT(($C$1:$C$20="Total")*($F$1:$F$20=39)*($D$1:$D$20))
		a better way maybe is to use a pivot table on my table above
		see next post

<colgroup><col><col><col><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[oldbrewer]

category	invoice	totcolumn	amounts	categoryexpanded	firsttwodigits
1905 - Doors				1905 - Doors	19
	Invoice #1		250	1905 - Doors	19
	Invoice #2		325	1905 - Doors	19
		Total	575	1905 - Doors	19
1945 - Windows				1945 - Windows	19
	Invoice #3		50	1945 - Windows	19
	Invoice #4		45	1945 - Windows	19
		Total	95	1945 - Windows	19
2905 - Maintenance				2905 - Maintenance	29
	Invoice #5		25	2905 - Maintenance	29
		Total	25	2905 - Maintenance	29
2975 - Repair				2975 - Repair	29
	Invoice #6		40	2975 - Repair	29
		Total	40	2975 - Repair	29
3905 - Construction				3905 - Construction	39
	Invoice #7		450	3905 - Construction	39
		Total	450	3905 - Construction	39
3925 - Equipment				3925 - Equipment	39
	Invoice #8		350	3925 - Equipment	39
		Total	350	3925 - Equipment	39
	firsttwodigits	19								firsttwodigits	39
				with a pivot table you say which firsttwodigits you want
	Sum of amounts			19 correctly gives						Sum of amounts
	totcolumn	Total		670						totcolumn	Total
	Total	670								Total	800
	(blank)	670								(blank)	800
	Grand Total	1340		copy the pivot table and select 39					Grand Total	1600
				gives 800
				now add them
				1470
				=B28 + L28

<colgroup><col><col><col><col><col><col><col span="4"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[oldbrewer]

category	invoice	totcolumn	amounts	categoryexpanded	firsttwodigits	selector
1905 - Doors				1905 - Doors	19	1	######		problem statement
	Invoice #1		250	1905 - Doors	19	1
	Invoice #2		325	1905 - Doors	19	1			we want to add the totals for any combination of firsttwodigits
		Total	575	1905 - Doors	19	1
1945 - Windows				1945 - Windows	19	1			choose any 2 digits
	Invoice #3		50	1945 - Windows	19	1
	Invoice #4		45	1945 - Windows	19	1			19
		Total	95	1945 - Windows	19	1			39
2905 - Maintenance				2905 - Maintenance	29	0
	Invoice #5		25	2905 - Maintenance	29	0
		Total	25	2905 - Maintenance	29	0			#####
2975 - Repair				2975 - Repair	29	0			=IF(OR(F2=$J$8,F2=$J$9),1,0)
	Invoice #6		40	2975 - Repair	29	0
		Total	40	2975 - Repair	29	0
3905 - Construction				3905 - Construction	39	1
	Invoice #7		450	3905 - Construction	39	1
		Total	450	3905 - Construction	39	1
3925 - Equipment				3925 - Equipment	39	1
	Invoice #8		350	3925 - Equipment	39	1
		Total	350	3925 - Equipment	39	1
				1470
				formula
				=SUMPRODUCT(($G$2:$G$21=1)*($C$2:$C$21="Total")*($D$2:$D$21))
				now you can build on this - I hope
				you can have say 10 cells J8 to J17 with "possible" codes
				say you choose 3 - other 7 will be blank
				put all 10 in the if(or( formula
				it will find no blanks so will not affect the calculation
				totally autometed !!!

<colgroup><col><col><col><col><col><col><col span="4"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Special-K99]

ignore this and see my post below

 

[Special-K99]

@Special-K99 - that's correct, but looking at your formula, I think we're really close.

I've been playing around with SUMIFS but I can't quite get it to work. I'm putting: =SUMIFS (D1:D20, C1:C20, "Total", E1:E20, >=1900, E1:E20, <=1999, E1:E20, >=3900, E1:E20, <=3999) but it isn't being accepted by Excel.

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

> = and < = need to be in quotes