technowerk
New Member
- Joined
- Oct 30, 2012
- Messages
- 5
Hi,
Following is a sample data I have. I have written a macro that does various calculations for me. However, now I'm stuck on coding SumProduct function.
<tbody>
</tbody>
This data is filtered data (I have applied advanced filter to get this data). Now I would like to sum 'Salary' when Employee=A AND Emp_grade=3.
As SUMIF works ONLY for 1 condition, I had to opt for SumProduct.
Here's what I've coded
This gives an error. I tried recording a macro and using the same code in my code but it doesn't work either. Can someone help please?
I do not want to use pivot table as the actual data is too large to fit on a single sheet of pivot table (about 95 columns and over 12000 rows) and thus there's a risk of losing data if I create a pivot table.
Is there any other function that I can use instead of SumProduct to combine 2 or more conditions in the same statement?
Thanks in advance!
Following is a sample data I have. I have written a macro that does various calculations for me. However, now I'm stuck on coding SumProduct function.
Company | Employee | Emp_grade | Year | Salary |
1 | A | 3 | 2000 | 2500 |
1 | B | 2 | 1997 | 4000 |
1 | A | 3 | 2002 | 2750 |
1 | C | 2 | 1998 | 5500 |
1 | D | 3 | 1998 | 1500 |
1 | A | 3 | 2003 | 3000 |
2 | A | 3 | 2004 | 3500 |
2 | E | 1 | 2000 | 6000 |
3 | A | 2 | 2005 | 3750 |
2 | A | 2 | 2005 | 4500 |
2 | F | 2 | 2001 | 4000 |
2 | D | 2 | 2001 | 6000 |
2 | G | 1 | 2001 | 7500 |
3 | A | 1 | 2008 | 5500 |
3 | B | 3 | 2000 | 2500 |
3 | F | 3 | 1990 | 1500 |
3 | H | 2 | 2004 | 5000 |
3 | I | 1 | 2010 | 7000 |
3 | J | 3 | 2000 | 3000 |
4 | F | 1 | 2008 | 5500 |
4 | K | 1 | 2009 | 6000 |
4 | A | 1 | 2009 | 5750 |
1 | A | 1 | 2010 | 6000 |
2 | A | 1 | 2011 | 7500 |
<tbody>
</tbody>
This data is filtered data (I have applied advanced filter to get this data). Now I would like to sum 'Salary' when Employee=A AND Emp_grade=3.
As SUMIF works ONLY for 1 condition, I had to opt for SumProduct.
Here's what I've coded
Code:
Dim TOT_SUM As Double
TOT_SUM=Application.WorksheetFunctions.SumProduct((B2:B65535="A"),(C2:C65535=3),E2:E65535)
This gives an error. I tried recording a macro and using the same code in my code but it doesn't work either. Can someone help please?
I do not want to use pivot table as the actual data is too large to fit on a single sheet of pivot table (about 95 columns and over 12000 rows) and thus there's a risk of losing data if I create a pivot table.
Is there any other function that I can use instead of SumProduct to combine 2 or more conditions in the same statement?
Thanks in advance!
Last edited: