Excel macro - sum based on a column name

Question

I'm trying to work out a way how to do the following:

1. Go to the Forecast Category Column
2. Filter by 'Pipeline'
3. Total the amount from the 'Amount' column
4. Paste the total next to the 'Pipeline =' cell.

Any help much appreciated.

Answer 1

You could do this in VBA, but just using formulas would be way easier:

=SUMIF(Table1[Forecast Category], "Pipeline", Table1[Amount])

in the cell you want to display your answer, where "Table1" is the name of your table. You can repeat the formula with "Best Case" and "Commit" to finish it all up.

It looks like you've already formatted it as a table, but if not, you can just select the table you've made, and click "Format as Table" in the styles group of the Home tab.

Answer 2

Instead of macro/vba use a formula for this:

=SUMIFS(B:B,F:F,"Pipeline") 'Or instead of Pipeline reference to your cell

If 'Forecast Category' is dynamic and you want to be able te refer to some other column:

=SUMIFS(B:B,INDIRECT(CHAR(MATCH("Forecast Category",1:1,0)+64)&":"&CHAR(MATCH("Forecast Category",1:1,0)+64)),"Pipeline") 'Also here, replace forecast category and pipeline for a cell reference if you like

Answer 3

Make your table into an actual table, and use a formula to sum the things you want to sum:

=SUMIF(Table3[Forecast];$E2;Table3[Amount])

Alternately you can type in the exact ranges you want to work on, but I prefer using tables.