Sum of Amount when two criteria are met

johnb1979

Board Regular
Joined
Dec 9, 2019
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hi, I'm sure you guys will be able to answer this one for me :)

I have a spreadsheet with 'Jan-Dec' and a 'Review' tab (13 tabs in total). The sheet is used to keep track of the value (£) of quotes for any given month.

This is what I'd like to happen;

If a job quoted in September was won in October, if the status on that quote is set to 'won' and the relevant 'win' month is also selected, I'd like the figures associated with that quote to be added to a cell on the 'Review' tab in the month that it was won (I hope that makes sense) - please see screen shot of the September tab completed as per my example.

I
1594396224762.png



I hope this all makes sense and please accept my thanks in advance!!!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
It can be done of course, but maybe should consider a different design? What if you had all of the months in one worksheet and then used a pivot table for your summary? You could likely get to your answers without the need for a formula. Furthermore, if the question gets asked about, can we look at it by discipline or SDP, you can quickly change the views without formulas.

Raw data:

ss1.PNG



Pivot Table:

ss2.PNG


And in a split second, I changed the view to Discipline:

ss3.PNG
 
Upvote 0
Thanks for your reply, this looks really good. Unfortunately, I need the formula for other parts of the spreadsheet too and the pivot won't work in that example.

Thanks anyway, really appreciate your input!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top