• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct, multiple text criteria, counting unique values only

jmaewyn

jmaewyn

New Member
Hi! I'm stuck on how to add a unique value restriction to a sumproduct formula that already counts 2 text arrays according to different criteria.

The situation:
Counting # attendees at different event locations, each of whom have been identified by name plus their company name. Sometimes more than one person attended a given event from the same company. My formula so far is fine when I want to count the # individuals, however since I also want to count # companies I need to only count the unique company names.

The data:
3 columns containg text only, all defined as dynamic named ranges: Event.Locations, Attendees.Individuals, Attendees.Companies

The formula so far:
=SUMPRODUCT((Event.Locations="Sydney")*(Attendees.Individuals<>""))
NB the "Sydney" criteria here is an example, as I use a cell reference in the actual workbook, & the <>"" criteria has been used as the column contains text only.

This formula produces a correct result of (say) 14 individuals.

Currently when I switch Attendees.Individuals with Attendees.Companies I also get a result 14, as the formula is simply counting the non blank cells. However I know from my data that only (say) 10 companies were actually represented at the event.

I've tried adding /COUNTIF(Attendees.Companies,Attendees.Companies) in to the formula, as Chandoo has previously recommended this as part of a SUMPRODUCT for counting unique values (as have others).
I've placed the COUNTIF within the formula as follows:
=SUMPRODUCT((Attendee.Locations="Sydney")*((Attendee.Companies<>"")/COUNTIF(Attendee.Companies,Attendee.Companies)))
The result is not 10 (using the example numbers, the result is 7.7916...).

I'm obviously doing something wrong, but I can't find a better alternative to adding /COUNTIF to an existing SUMPRODUCT. I've read that FREQUENCY can only be used for numerical values, so I've not looked further into that, & I'm unsure how/whether to try INDEX or MATCH given the base formula is SUMPRODUCT.

Does anyone have any ideas?
NB I'd prefer to stay away from VBA as the report will be handed over to people with very little Excel experience.
Data can be provided if required.
 
Hi,

please upload a sample file, with the inputs and the Expected output, I am sure that someone will work out the working solution
 
My work version of windows is ancient, & IE8 seems incapable of uploading an xl file directly, sorry. Here's the link to the doc, which I've uploaded to my Google Drive - please let me know if there are access issues.
 
From that test it looks like the link is fine - just download the file from the Viewer to see the formulae etc. NB for some reason the formula including /COUNTIF produced a different result in this example file from the original - not sure why this has happened, nothing very obviously different between the 2.
 
Hi, jmaewyn!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, let us go.

1st, a slight tweak to your dynamic named ranges definition (which have a nice naming structure): after removing the unnecessary cells at row 1 (C1), if you have nothing below row 25000, you'd change the references like:
COUNTA($F$4:$F$25000)
by:
COUNTA($F:$F$)-1

Now, to your problem.

The problem with the reciprocal COUNTIF is that you were considering all the company entries including those which didn't correspond to the actual location. If you remove the location constraint then the formula will retrieve an integer number, corresponding to the whole no. of companies.

To force COUNTIF counting only those of a specific location it'd be very hard, in fact impossible, but with COUNTIFS it's easy: just add pairs of range and criteria.

Formulas:
E6: =SUMAPRODUCTO((Event.Locations=C6)*(SI.ERROR(1/CONTAR.SI.CONJUNTO(Attendee.Companies;Attendee.Companies;Event.Locations;C6);0))) -----> in english: =SUMPRODUCT((Event.Locations=C6)*(IFERROR(1/COUNTIFS(Attendee.Companies,Attendee.Companies,Event.Locations,C6),0)))

That will avoid counting companies off the location.

With the same logic, if an individual may attend for more than one company, you should use in D6 a similar formula.

Copy down D6:E6 as required.

Just advise if any issue.

Regards!
 
Hi SirJB7!
Thanks very much for your quick response & elegant solution - thanks also for suggesting a tidy up of the COUNTA, I'll use that syntax in future.
Unfortunately my work is stuck in a timewarp & I'm using Excel 2003. I know I need to use IF(ISERROR(...)) instead but in changing the formula I'm getting the error result of 0:
=SUMPRODUCT((Event.Locations=C6)*(IF(ISERROR(1/COUNTIFS(Attendee.Companies,Attendee.Companies,Event.Locations,C6)),0,(1/COUNTIFS(Attendee.Companies,Attendee.Companies,Event.Locations,C6)))))
 
Hi, jmaewyn!

Give a look at this file.
https://dl.dropboxusercontent.com/u...nique-values (for jmaewyn at chandoo.org).xls

It's a .xls with this array formula at column E:
E6: =SUMAPRODUCTO((Event.Locations=C6)*(SI(ESERROR(1/CONTAR.SI.CONJUNTO(Attendee.Companies;Attendee.Companies;Event.Locations;C6));0;1/CONTAR.SI.CONJUNTO(Attendee.Companies;Attendee.Companies;Event.Locations;C6)))) -----> in english: =SUMPRODUCT((Event.Locations=C6)*(IF(ISERROR(1/COUNTIFS(Attendee.Companies,Attendee.Companies,Event.Locations,C6)),0,1/COUNTIFS(Attendee.Companies,Attendee.Companies,Event.Locations,C6))))

Just advise if any issue.

Regards!
 
Hi Somendra Misra & SirJB7
Sorry for being a pain, but entering the above as an array (ctl+shift+ent {}) is still only producing a result of 0, when I know that in the first cell the result should be 10. It seems to me that because of the logic in the IF(ISERROR... statement, Excel is picking the error result (...,0,...). I can't work out where the error is though.
SirJB7, you'll see that result in the file linked above.
If you have any other ideas I'd be very thankful!
Regards,
Jess
 
Brilliant work, Somendra! Thank you so much! I promise I'll spend some time tomorrow parsing this formula to learn more about how it operates ;)
 
Hi, jmaewyn!
Glad you solved it, but the formula I posted it's the same as in the uploaded file which I downloaded and checked it works. If you have any issue regarding that implementation, please upload you actual file.
Regards!
 
Somendra Misra said:
hi Jess,

Just see the attached file, with a different formula, again an array formula, so ctrl+shift+enter is must.

Advise if any issue.

Regards!
Click to expand...
Hi Somendra,
This is a brilliant solution. Just two quick follow ups,
1) Is there a non-array solution to this problem?
2) This one had one unique qualifier, e.g. Location & then the formula counted unique items from the companies column. What if you had 2 or more unique qualifiers & then the formula counted the unique items from the companies column?

Your Thoughts?

Regards,

Ray
 
Rfinweld said:
Hi Somendra,
This is a brilliant solution. Just two quick follow ups,
1) Is there a non-array solution to this problem?
2) This one had one unique qualifier, e.g. Location & then the formula counted unique items from the companies column. What if you had 2 or more unique qualifiers & then the formula counted the unique items from the companies column?

Your Thoughts?

Regards,

Ray
Click to expand...
Hi Ray,

1. There is a possibility of non-array solution, if we use helper columns.
2. If you can upload your file with the variant that you mentioned than we can work out a solution for it.

Regards,
 
Actually somendra, I figured it out. I executed the solution using your method, i.e. sum(if) and also my fav, sumproduct. I'll share with you a little later. But in both cases I was forced to use it as an array solution. The challenge for me now is to figure out a non-array solution without any helper columns.

Regards,

Ray
 
You must log in or register to reply here.
Back
Top