Sum of Amount when two criteria are met

johnb1979

New Member
Joined
Dec 9, 2019
Messages
39
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Multiverse

New Member
Joined
Nov 15, 2019
Messages
16
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
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,282
Members
418,185
Latest member
snoogz2

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