I think I need to use SUMPRODUCT but can not make it work

[missingdigit]

I need to count the number of times a Salesperson sells a product based the product or products and on a particular dollar value range.

I need to know how many times Bob sold a cup that has a value between $1 and $5 or how many times he sold anything that was between $1 and $5.

B2:B11 C2:C11 D2:D11 F2:F11 G2:G11
List of List of Salesperson $ Each Item List of all products List of Salespeople
Products Sold who made Sale sold for

What I am trying to use only counts the number of cups sold and does not filter for the dollar value.

=SUMPRODUCT((C2:C11=G2)*(B2:B11=F5)*(D2:D11>=1)*(D2:D11<=5)) When I use this it returns the total of the cups sold in the dollar range. But when I add multipe products the formula does not work. =SUMPRODUCT((C2:C11=G2)*(B2:B11=F2:F11)*(D2:D11>=1)*(D2:D11<=5))

Any suggestions?

[Debraj Roy]

Hi missingdigit!

Welcome to the forum...
Aren't you missing Something..

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Christopherdj]

Hi missingdigit
You may need to use an array to look up your F2:F11 reference. But without an uploaded sheet one cannot determine what exactly it is.
Also, I think your formulas should be inserted inside Code tags (see#) on either side.

[Danerida]

Try:

Please Login or Register  to view this content.

Confirmed with CONTROL + SHIFT + ENTER as an array formula

Dan

[Tony Valko]

But when I add multipe products the formula does not work. =SUMPRODUCT((C2:C11=G2)*(B2:B11=F2:F11)*(D2:D11>=1)*(D2:D11<=5))

Any suggestions?

Maybe something like this...

=SUMPRODUCT(--(C2:C11=G2),--ISNUMBER(MATCH(B2:B11,F2:F11,0)),--(D2:D11>=1),--(D2:D11<=5))

[Christopherdj]

Please Login or Register  to view this content.

as an alternative, remember to ALT+Ctrl+Enter for array formulas, it will add {} to either end.