I have a budget, with consultants’ names, wether or not they are inhouse (yes/no), then 12 columns for time spend in every month. In a Data sheet (with among other information) their hourly rate, which varies per month. I need to calculate in sheet 1, as a total “below” the sum of hours per month, the cost of the External consultants and the cost of my inhouse consultants as seperate subtotals. So far I have :{=SUM(IF($H$52:$H$74=”no”,L$52:L$74*(VLOOKUP($E87:$E108,’Data Sheet’!$B$3:$R$36,HLOOKUP(‘Data Sheet’!G$1,’Consult Check’!L$1:W$1,1,0)-39,0))))}; column H defines Inhouse Yes/No; column L = month 1′s hours; column E = Consultant’s name; Data sheet row 1 is a number of the month as this is an ongoing budget for more than one year, matching it with the number in my current sheet, to ‘extract’ the correct month’s hourly cost. Unfortunately, the formula uses the first consultant’s cost and not the specific consultant’s cost. Other than “manually” selecting each consultant’s hours and the corresponding rate, what formula can I use? To add: there are several projects and several consultants working on them, some off course on more than one project per month. As the ‘sales manager/director’ have to complete this section, I want to ‘bullet proof’ the formulas so that in case of any changes made, different consultant selected than ‘version 1′, I do not have to ‘redo’ the specific formulas to ensure the costing is correct. I would also prefer not to have to add an additional sheet to do these calculations on.
SumProduct with multi conditions
- Thread starter Adell
- Start date
I might have missed it, but is there some cell somewhere that defines an external/internal consultant? Either way, I think the easiest way will be to use a helper column on the sheet with the 12 months. The formula in that column would be:
Psuedo:
=SUMPRODUCT(RangeHoursforYr,INDEX(AllEmployeeRates,MATCH(EmployeeName,ListofEmployeeNames,0),0))
I think it should look like this:
=SUMPRODUCT(L52:W52,INDEX('Data Sheet'!C$3:N$108,MATCH(E52,'Data Sheet'!B$3:B$108,0),0))
Then, to get your totals for in house, it's simply:
=SUMIF(H52:H74,"yes",HelperColumn)
Psuedo:
=SUMPRODUCT(RangeHoursforYr,INDEX(AllEmployeeRates,MATCH(EmployeeName,ListofEmployeeNames,0),0))
I think it should look like this:
=SUMPRODUCT(L52:W52,INDEX('Data Sheet'!C$3:N$108,MATCH(E52,'Data Sheet'!B$3:B$108,0),0))
Then, to get your totals for in house, it's simply:
=SUMIF(H52:H74,"yes",HelperColumn)
Thanks, I get a "value" return on the formula? the MATCH section's answer is correct (row number), the INDEX section's answer is correct (hourly rate for the consultant). In the main sheet, Column L is hours for January, column M is hours for Feb etc. I need to calculate the costs of "External consultants" (hours * rate ph) for January; Feb; Mar etc (each column) as well as a seperate line, the monthly cost of the "internal consultants" per project, as it is my monthly "cost of sales" and not annual cost that I am looking for.
Hmm. After some testing and looking around, I'm not sure there's a native way to do a "array lookup". Thankfully, I can write a UDF to do that. =)
Right click on sheet tab, view code, goto Insert - Module. Paste this in:
[pre]
[/pre]
Function's arguments are defined as so:
CritRange: Range of cells that determine which rows to include
xCriteria: What the CritRange is looking for
LookupRange: Range of cells you want to lookup a value for
SearchRange: List of all values the LookupRange will be looking in
ReturnedRange: Corresponding range of SearchRange that you want value from
Then, in your worksheet, the formula becomes:
=SUMPRODUCT(L52:L74,ArrayLookup($H52:$H74,"yes",$E52:$74,'Data Sheet'!$B$3:$B$108,'Data Sheet'!C$3:C$108))
This will return all the in-house employees value for the first month. Copy formula to the right to get the following months. Just change the "yes" to a "no" to get external employees.
Right click on sheet tab, view code, goto Insert - Module. Paste this in:
[pre]
Code:
Function ArrayLookup(CritRange As Range, xCriteria As String, LookupRange _
As Range, SearchRange As Range, ReturnedRange As Range)
Dim Result() As Single
ReDim Result(1 To LookupRange.Count) As Single
i = 1
For Each c In LookupRange
If CritRange.Cells(i, 1).Value = xCriteria Then
Result(i) = WorksheetFunction.Index(ReturnedRange, _
WorksheetFunction.Match(c.Value, SearchRange, 0))
Else
Result(i) = 0
End If
i = i + 1
Next c
If LookupRange.Rows.Count > 1 Then
ArrayLookup = Application.Transpose(Result)
Else
ArrayLookup = Result
End If
End FunctionFunction's arguments are defined as so:
CritRange: Range of cells that determine which rows to include
xCriteria: What the CritRange is looking for
LookupRange: Range of cells you want to lookup a value for
SearchRange: List of all values the LookupRange will be looking in
ReturnedRange: Corresponding range of SearchRange that you want value from
Then, in your worksheet, the formula becomes:
=SUMPRODUCT(L52:L74,ArrayLookup($H52:$H74,"yes",$E52:$74,'Data Sheet'!$B$3:$B$108,'Data Sheet'!C$3:C$108))
This will return all the in-house employees value for the first month. Copy formula to the right to get the following months. Just change the "yes" to a "no" to get external employees.
Thanks, done all of above, but results are Zero? could it be because column H is lookup formula returning "yes" or "no" as well as the information in Data sheet, column C-Q ?(I have never had this before, but have read that with some formulas, it looks as formula in a cell rather than the return of the formula.
No, it shouldn't...VB is case-sensitive however. Is col H EXACTLY "yes"/"no", or is it "Yes"/"No"?
To get rid of the case sensitivity, replace this line:
[pre]
[/pre]
To get rid of the case sensitivity, replace this line:
[pre]
Code:
If CritRange.Cells(i, 1).Value = xCriteria Then
with this:
If UCase(CritRange.Cells(i, 1).Value) = UCase(xCriteria) Then