Sumifs or Sumproduct With Multiple Conditions Help Please!

**geepee** · 03-29-2013, 03:42 PM

Hi!

I am in the middle of putting this model together for my consulting company and have searched the internet far and wide without any luck.

In cells F3:F12 I have a drop-down where there is an option to select the 'Project Type' of 'External' or 'Intercompany.'
For instance, if 'External' is selected in cell F3, I need G3 to pull from the 'External Bill Rate' cells G25:G34 dependent on the last name of the employee. So in this instace Doe1 would have an 'External bill rate' of $220.
If 'Intercompany' is selected in cell F3, I need G3 to pull from the 'Intercompany Bill Rate' cells F25:F34 dependent on the last name of the employee. In this instance, Doe1 would have an 'Intercompany Bill Rate' of $195.

Depending on what I select in cells F3 to F12 , I need cells G3:G12 'Bill Rate' to display the correct result. I started witth a sumif formula but am unsure of where to head next. I am using Excel 2007.

I hope this is clear. Please see attached file. Thank you so much for taking a look at this!

**icestationzbra** · 03-29-2013, 03:58 PM

hi there,

try the following formula in G3, then drag-fill down:

Please Login or Register  to view this content.

1201

**geepee** · 03-29-2013, 04:09 PM

THat did the trick!! THank you so much for your help icestationzbra! You just saved me for Monday morning. Thanks Again!!

**geepee** · 05-01-2013, 09:27 AM

Hi icestationzbra, I was wondering if you can work your magic with this issue that I have on the same spreadsheet. Thanks for looking at this.

I am in the middle of putting this model together for my consulting company and have searched the internet far and wide without any luck.

In cell N3, I need a true formula to conditional format the cell to turn a different color as a warning if it is true.
For instance, ‘Doe1’ in cell A3 and A23 should not exceed 1.00 when summed in each of the monthly columns, for example column ‘N’ ‘Apr-13’. If ‘Doe1’ were to exceed 1.00 when summed for any of the months, the cell would turn light red. The reason for this is because each employee can not work more than 100% (1.00 in any month).
I hope this is clear any any help would be much appreciated.
Thank you for looking at this!