Not certain if this is possible...

STEMALO

Board Regular
Joined
Apr 15, 2008
Messages
93
Hi everyone,

I have a list of activities that are grouped per quarter. Each activity has a vendor and an activity code. Tracking ESTIMATED Revenue, ESTIMATED Expenses, ESTIMATED Profit (EST REV - EST EXP).
Then in Monthly columns (JAN to DEC) I enter the actual Revenue that i did. Goal is to be able to filter by vendor and quarter to see what actual profit i made.
So request is to be able to select vendor and quarter and have certain columns calculate on their own.
here is a sample
VendorQuarterWBS#PA#ActivityESTIMATED RevenueESTIMATED ExpenseESTIMATED ProfitMonth billingJANFEBMARAPRMAYJUN
COMPANY ABCQ1M-123456123456National Sales Conference$ 3,000$ 1,000$ 2,000March$ -$ -$ 3,000.00
COMPANY ABCQ1M-123456123456BLA BLA$ 4,000$ -$ 4,000March$ -$ -$ -
COMPANY ABCQ1M-123456123456BLA BLA BLA$ 10,000$ -$ 10,000March$ -$ -$ -
COMPANY ABCQ1M-123456123456BLA BLA BLA BLA$ 5,000$ 3,000$ 2,000March$ -$ -$ -
COMPANY ABCQ1M-123456123456YO$ 7,000$ -$ 7,000March$ -$ -$ 5,000.00
COMPANY ABCQ1M-123456123456YO YO$ 7,000$ 2,000$ 5,000March$ -$ -$ 8,000.00
COMPANY ABCQ1M-123456123456YO BLA$ 25,000$ 10,000$ 15,000March$ -$ -$ 16,000.00
COMPANY ABCQ1M-123456123456Demo Units$ 3,500$ 2,000$ 1,500March$ -$ -$ 1,000.00
COMPANY ABCQ1M-123456123456Store Giveaway$ 3,000$ 1,000$ 2,000March$ -$ -$ -
COMPANY ABCQ1SubTotal$ 67,500$ 19,00048,500ACTUAL PROFIT$ -$ -$ 33,000
COMPANY ABCQ1TARGET PROFIT$ 48,500
COMPANY ABCQ1DELTA PROFIT$ -$ --$ 15,500
COMPANY ABCQ2M-123456123456SOMETHING$ 4,000$ -$ 4,000June$ -$ -$ 25,000
COMPANY ABCQ2M-123456123456SOMETHING NEW$ 15,000$ 5,000$ 10,000June$ -$ -$ 10,000
COMPANY ABCQ2M-123456123456SOMETHING OLD$ 7,000$ -$ 7,000June$ -$ -$ 7,000
COMPANY ABCQ2M-123456123456BLA BLA$ 10,000$ 10,000June$ -$ -$ -
COMPANY ABCQ2M-123456123456BLA BLA BLA$ 10,000$ 10,000June$ -$ -$ -
COMPANY ABCQ2M-123456123456YO BLA$ 1,000$ 700$ 300June$ -$ -$ -
COMPANY ABCQ2M-123456123456Demo Units$ 2,000$ 400$ 1,600June$ -$ -$ 1,000
COMPANY ABCQ2M-123456123456Store Giveaway$ 40,000$ 33,600$ 6,400June$ -$ -$ 6,000
COMPANY ABCQ2M-123456123456National Sales Conference$ 20,000$ 12,000$ 8,000June$ -$ -$ 83,000
COMPANY ABCQ2SubTotal$109,000$51,700$57,300ACTUAL PROFIT$ -$ -$ 132,000
COMPANY ABCQ2TARGET PROFIT$ 35,000$ 20,000$ 85,000
COMPANY ABCQ2DELTA PROFIT-$ 35,000-$ 20,000$ 47,000
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

STEMALO

Board Regular
Joined
Apr 15, 2008
Messages
93
Let me rephrase, because it is confusing...
I want to be able to apply filter on various columns and they have the columns that contain values summed up.
 

STEMALO

Board Regular
Joined
Apr 15, 2008
Messages
93
a bit lit SUMIFS but instead of hardcoding everything i would use the sort & filter, so if i sort COMPANY ABC with Q1 it would do the sum/subtotal
 

Watch MrExcel Video

Forum statistics

Threads
1,132,702
Messages
5,654,820
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top