SUMPRODUCT Formula Help Please

[Joe Gieder]

First thank you for looking at this and helping and soory for the long post.
I'm not sure if I can relay what I'm doing without the spreadsheet but what
I have is this. I have about 700 rows of data with many columns, two columns
of information I want to use are vendor name (column G) and file name (column
A). What I am trying to do is use the file name as one filter and then count
how many vendors are listed with that same file name (vendors can be listed
multiple times but I only what to count each one once, there can also be a
blank cell for no vendor listed). I have tried this formula,
=SUMPRODUCT(--($A$4:$A$658='Summary
Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when
I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary
Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should
be 27.
Is there way a formula like this can work?

TIA
Joe

 

[Dave F]

Why don't you create a pivot table?

File name would be the column and drop the vendor in the data field and have
the count function count the number of vendors per file name?

If this doesn't address your needs, please repost.

Dave

 

[Domenic]

Try...

=COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary
Data'!E1,IF($G$4:$G$658<>"",MATCH($G$4:$G$658,$G$4:$G$658,0))),ROW($G$4:$
G$658)-ROW($G$4)+1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Joe Gieder]

I didn't think of a pivot table.
I just tried the pivot table and it counts all the vendors (even the dups),
I need to count each one once only, if it appears three times it only counts
as one.
Another thing I do is after I get this result I use the total number of
unique vendors and find out how many have responded and divide these two
numbers to come up with a % of how may are complete.

Thanks for you help
Joe

 

[Dave F]

Hm, I missed the requirement of dupes.

1) Concatenate the vendor and file names in a helper column so that you can
filter on this helper column. =CONCATENATE(A1,B1) if the vendor and file
name are in column A and column B
2) Apply the advanved filter tool to extract unique records. See here for
instructions: http://www.contextures.com/xladvfilter01.html Scroll half way
down to "Filter Unique Records"
3) Create a pivot table off the filtered records.

Dave

 

[Joe Gieder]

Thanks Dominic, this worked and was what I was looking for. Now can I take
this one step further by including $J$4:$J$658<>"" to count only the ones
that have a price?

I tried by putting

=COUNT(1/FREQUENCY(IF(AND($A$4:$A$658='Summary
Data'!E1,$J$4:$J$658<>""),IF($G$4:$G$658<>"",MATCH($G$4:$G$658,$G$4:$G$658,0))),ROW($G$4:$
G$658)-ROW($G$4)+1))

but it didn't work. I also tried other various places but to no avail.

Thank you
Joe

 

[Joe Gieder]

Dave,
Thanks for your help but the pivot table just wasn't what I was looking for
but it seems like a viable sollution.

Thank you for your help
Joe

 

[Domenic]

The AND function only returns a single result. So it won't work with
arrays. Try...

=COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary
Data'!E1,IF($J$4:$J$658<>"",IF($G$4:$G$658<>"",MATCH($G$4:$G$658,$G$4:$G$
658,0)))),ROW($G$4:$G$658)-ROW($G$4)+1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Bob Phillips]

=COUNTIF($A$4:$A$658,'Summary Data'!E1)

=COUNTIF($A$4:$A$658,'Summary Data'!G1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joe Gieder]

Thank you again for the help, it worked perfectly.

 

[Joe Gieder]

Bob,
It works to count all the vendors but I only need to count the unique names.

Thank you for your help.

Joe

 

[Harlan Grove]

The AND function only returns a single result. So it won't work with
arrays. Try...

=COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary Data'!E1,
IF($J$4:$J$658<>"",IF($G$4:$G$658<>"",
MATCH($G$4:$G$658,$G$4:$G$658,0)))),ROW($G$4:$G$658)-ROW($G$4)+1))

....

Lots of IF calls. They may improve recalc speed, but they limit
generality by using up nested function call levels. A possibly slower
alternative,

=COUNT(1/FREQUENCY(IF(($A$4:$A$658='Summary Data'!E1)
*($J$4:$J$658<>"")*($G$4:$G$658<>""),
MATCH($G$4:$G$658,$G$4:$G$658,0)),ROW($G$4:$G$658)-ROW($G$4)+1))

allows for a greater number of conditions, subject to the formula
length limit.

 

[Domenic]

I'm not so sure I'd be willing to sacrifice efficiency in favour of
generality. I'm guessing that in some instances the difference in
efficiency can be significant.