COUNTIF Problem - Formula returns #Value

**MCoev** · 03-01-2012, 02:48 PM

Please Login or Register  to view this content.

So I am looking to create a tally of the number other businesses that customers visit when they visit a specific store. Surveys were done at around 50 stores, so $D$9 contains the name of the one I want to focus on. Columns I:P contain the names of other stores that people visited after the one that they completed the survey in. I want to count, in every row where Column E equals D9, how many times the names of other stores are mentioned (the name of the other store is in B10 in this case).

With that code I get #VALUE message, the code below worked for the data as a whole, but now i want to specify each business.

Please Login or Register  to view this content.

Thank you for any help with this!

Michael

**daddylonglegs** · 03-01-2012, 02:56 PM

With COUNTIFS you need to have both ranges the same size so you can't use COUNTIFS unless you count each column I to P separately. Try using SUMPRODUCT, ideally with a restricted range so assuming data in rows 2 to 100 (change as required)

=SUMPRODUCT(('BusinessData'!$E$2:$E$100=$D$9)*('BusinessData'!$I$2:$P$100=B10))

**MCoev** · 03-01-2012, 03:21 PM

Originally Posted by daddylonglegs

With COUNTIFS you need to have both ranges the same size so you can't use COUNTIFS unless you count each column I to P separately. Try using SUMPRODUCT, ideally with a restricted range so assuming data in rows 2 to 100 (change as required)

=SUMPRODUCT(('BusinessData'!$E$2:$E$100=$D$9)*('BusinessData'!$I$2:$P$100=B10))

Thank you! Still having some issues though, I have text in the cells, not numbers so I think that is messing up the SUMPRODUCT. i tried using an IF statement to no avail:

Please Login or Register  to view this content.

Am I doing something wrong? Any other suggestions?

Thank you,
Michael

**Pepe Le Mokko** · 03-01-2012, 03:32 PM

Try

Please Login or Register  to view this content.

**daddylonglegs** · 03-01-2012, 03:35 PM

That doesn't work, Pepe, it'll give you #VALUE! error. If the ranges are different sizes (multiple columns in one as here) then you need to multiply with * to construct a single array

The original SUMPRODUCT I suggested should work - it doesn't matter that there is text in the cells SUMPRODUCT is effectively testing the conditions and counting each TRUE as 1.

In your suggested formula, Michael, one range ends at row 539 and one at 540, you need to make those both the same, so using my suggested formula with revised ranges that should be this:

=SUMPRODUCT(('BusinessData'!$E$2:$E$540=$D$9)*('BusinessData'!$I$2:$P$540=B10))