College Excel Upcoming Grad and Need Help! Please!

[ross_boss08]

Hey all,

I just created my account today becuase I could see the level of intelligence that is being shared across this forum. I recently got an internship at a industrial company and I'm having a pain in the *** trying to get this model I am building for them to work.

May seem simple for some of you.

Here's the example:

Customer Name Fiscal Month Num Gross Sales
Advance 1 $500
Car 2 $1500
Car 1 $2000
Advance 1 $2500

For this example, I would want the output to be $3000 because it matches both Advance as the Customer Name AND April, aka 1, for the fiscal month code.

What I need is a function that checks BOTH the customer name and fiscal month num are what I want to then pull the gross sales and sum up every instance found. I've tried SUMPRODUCT, SUMIF, and nested VLOOKUPS and it seems I am missing something, but don't know. I can get the gross sales for one constraint by using SUMIF, but not TWO.

Please help and your advice is much appreciated!

 

[jakeman]

try this function:

=SUMPRODUCT(--($A$2:$A$5="Advance")*($B$2:$B$5=1)*($C$2:$C$5))

 

[ross_boss08]

Thank you so much!! jakeman, I believe my version did not work because of the "--" you have in your function, can you explain what that does?

 

[shyy]

If you have excel 2007 use SUMIFS instead of SUMPRODUCT

=SUMIFS($C$2:$C$5,$A$2:$A$5,"Advance",$B$2:$B$5,1)

 

[ross_boss08]

I do use Excel 2007, why use this over sumproduct? More accurate? Less room for error?

 

[jakeman]

I believe it is used when one part of your comparison is a non-numeric value. So in this case, you needed it to compare "Advance" along with the numeric values of Fiscal Month num and Gross Sales.

 

[shyy]

I believe the -- converts it to binary 1 or 0 to represent true and false.

Both are accurate but I believe the SUMPRODUCT is more volatile. SUMIF would be faster. Someone can chime in on this if I am wrong.

 

[nuked]

Courtesy of Barry Houdini:

SUMIFS, SUMIF, COUNTIF and COUNTIFS only accept range arguments, not arrays. Typically any function applied to the range, TEXT or YEAR for instance, will result in an array......so best to stick with SUMPRODUCT.

Incidentally this also explains why SUMIF and similar don't work with closed workbooks, because a range extracted from a closed workbook becomes an array.....

 

[shyy]

Just tested, if I know the SUMIF is not using a range from another workbook, I will keep it but anything more complicated I would use SUMPRODUCT. Thanks