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!


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1905 - Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$250[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$325
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]$575[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1945 - Windows[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$50[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$45[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]$95[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]2905 - Maintenance[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #5[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$25[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]$25[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]2975 - Repair[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #6[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$40[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]$40[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]3905 - Construction[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #7[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$450[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]$450[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]3925 - Equipment[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Invoice #8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]$350[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]$350[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1205"]
<colgroup><col><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD]1905 - Doors[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1905 - Doors[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="colspan: 4"]very easy to make a helper column[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #1[/TD]
[TD] [/TD]
[TD]250[/TD]
[TD]1905 - Doors[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="colspan: 4"]to put the col A titles on every relevant row[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #2[/TD]
[TD] [/TD]
[TD]325[/TD]
[TD]1905 - Doors[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="colspan: 6"]and to put the first two digits into a second helper column[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD]575[/TD]
[TD]1905 - Doors[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]670[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1945 - Windows[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1945 - Windows[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #3[/TD]
[TD] [/TD]
[TD]50[/TD]
[TD]1945 - Windows[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #4[/TD]
[TD] [/TD]
[TD]45[/TD]
[TD]1945 - Windows[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD]95[/TD]
[TD]1945 - Windows[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2905 - Maintenance[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2905 - Maintenance[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #5[/TD]
[TD] [/TD]
[TD]25[/TD]
[TD]2905 - Maintenance[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]your dollar amounts appear to be text[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD]25[/TD]
[TD]2905 - Maintenance[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2975 - Repair[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2975 - Repair[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #6[/TD]
[TD] [/TD]
[TD]40[/TD]
[TD]2975 - Repair[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD]40[/TD]
[TD]2975 - Repair[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3905 - Construction[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3905 - Construction[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD="colspan: 2"]formula giving 670[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #7[/TD]
[TD] [/TD]
[TD]450[/TD]
[TD]3905 - Construction[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD="colspan: 7"]=SUMPRODUCT(($C$1:$C$20="Total")*($D$1:$D$20)*($F$1:$F$20=H4))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD]450[/TD]
[TD]3905 - Construction[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3925 - Equipment[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3925 - Equipment[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Invoice #8[/TD]
[TD] [/TD]
[TD]350[/TD]
[TD]3925 - Equipment[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD]350[/TD]
[TD]3925 - Equipment[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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....