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		very easy to make a helper column
	Invoice #1		250	1905 - Doors	19		to put the col A titles on every relevant row
	Invoice #2		325	1905 - Doors	19		and to put the first two digits into a second helper column
		Total	575	1905 - Doors	19		19	670
1945 - Windows				1945 - Windows	19		29	65
	Invoice #3		50	1945 - Windows	19		39	800
	Invoice #4		45	1945 - Windows	19
		Total	95	1945 - Windows	19
2905 - Maintenance				2905 - Maintenance	29
	Invoice #5		25	2905 - Maintenance	29			your dollar amounts appear to be text
		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		formula giving 670
	Invoice #7		450	3905 - Construction	39		=SUMPRODUCT(($C$1:$C$20="Total")*($D$1:$D$20)*($F$1:$F$20=H4))
		Total	450	3905 - Construction	39
3925 - Equipment				3925 - Equipment	39
	Invoice #8		350	3925 - Equipment	39
		Total	350	3925 - Equipment	39

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

 

[Special-K99]

in E1
=LEFT(A1,4)

in E2
=IF(A2<>"",LEFT(A2,4),F1)
and copy E2 down the column

Column D needs to be numbers not text with a $, if you have $ remove the dollar sign unless that column is a number formatted with a $.

Then use this

=SUMPRODUCT(((E1:E20>1900)*(E1:E20<=1999))+((E1:E20>=3900)+(E1:E20<=3999))*(C1:C20<>"Total")*(D1:D20))

Total in your example should be 1535

 

[Fennek]

Hi,

als a starting point, result in the debug-window:

Sub Fen()
Cells(Rows.Count, 4).End(xlUp).Offset(1, -3) = "@"
For Each Ar In Columns(1).SpecialCells(2).Areas
    If InStr(1, "1939", Left(Ar.Cells(1), 2)) > 0 Then
        Debug.Print Ar.Row, Ar.End(xlDown).Offset(-1, 3)
    End If
Next Ar
End Sub

The should be a space-line before the first code.

 

[wesleyterrill]

The total should be 1470. 575+95+450+350=1470. I need the 1900 AND 3900 groups.

Ideally, I'd like to just dump the raw data and have a formula pull the necessary info. Since my csv will be different every time, I don't want to create new columns for every report.

But I might have to go with this process, thanks!

 

[Special-K99]

Yep total shoul dbe 1470.
My formula's bringing in the 2900 series too.

 

[oldbrewer]

I got 10/10 for sums....

 

[wesleyterrill]

@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], ...)

 

[wesleyterrill]

@oldbrewer what do you mean 10/10? Like they came out 100% correct?

Your method requires extra steps - I'm just worried that it can't be standardized for every report. How would you alter that formula to include the 3900 invoices as well? Ideally, I'll just plug in the data and have my total ($1470) instead of having to go back and add up all the totals I need.

 

[oldbrewer]

I calculated for all number groups - from that table you can make a new table with only your preferred groups - but I will go back and mod it for you....