• 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.

Need to overcome a data validation dropdown list restriction

Grumpy88

Grumpy88

Member
Hi.


Shrivallabha was kind enough to help me in my previous post "Need a COUNT formula with multiple AND and OR criteria" with solving my issue to develop a formula that counts the number of records in a table that simultaneously complies with multiple compulsory and optional criteria.


To recap the situation briefly, I needed a formula to count the number of records in which the Product ="Endowment" AND the Branch ="Cape Town", but also in which the Marketer ="Smith" OR ="Jones". I had the Marketers in two different cells (D2 and E2), using dropdown data validation lists to select and display their names.


Shrivallabha then came up with the following formula, which worked perfectly:


=SUMPRODUCT(--($A$2:$A$11="Endowment"),--($B$2:$B$11="Cape Town"),(($C$2:$C$11=D2)+($C$2:$C$11=E2)))


However, is there a formula-based way to tweak my current arrangement so that if I want to see the data for all Marketers, rather than for one or two specifically selected from the dropdown lists in D2 and E2, I can do so (in D2 for instance) by just not selecting anyone (or by pressing Delete to clear the previous selection)?


At the moment as soon as I clear D2 (and E2), the formula resets the results to zero because the source cell is then empty. Ideally an "All" selection on the dropdown list would be best, but because the list content comes from data validation of the entries within the source table, there is no "All" item in the table's Marketers column.


Thanks.
 
To see all marketers take that section out altogether


=SUMPRODUCT(($A$2:$A$11="Endowment")*($B$2:$B$11="Cape Town"))
 
Hi Graeme ,


If you wish to have one formula , which covers all cases , then you can use helper cells D4 and E4 , which will modify D2 and E2 according to your choices.


In D4 , put in the following formula :


=IF(OR(D2="",E2=""),"",D2)


In E4 , put in the following formula :


=IF(D4="","",IF(D4<>E2,E2,"ZZZZZZZZZZZ"))


Your original SUMPRODUCT formula is modified as follows :


=SUMPRODUCT((($A$2:$A$11="Endowment")*($B$2:$B$11="Cape Town")),(((LEFT($C$2:$C$11,LEN(D4))=D4)+(LEFT($C$2:$C$11,LEN(E4))=E4)))*IF(D4="",0.5,1))


This technique is courtesy :


http://www.mrexcel.com/forum/excel-questions/163687-sumproduct-wildcards.html


Narayan
 
Thanks Narayan.


I'll give your formula a try, but maybe for now I should just apply the KISS approach and just remove the fields altogether. I will lose some functionality, but nothing that I can't live without.


By the way, are you still chasing up leads on my other Cape Town Cricket Club project, or is that on the backburner now? I haven't followed up for a while now, because my priority on that of late has been just to capture the basic data before time runs out. Thus absolutely no pressure or expectations from my side - was just wondering!
 
Going with a simple approach, why not use an IF function? makes it easy to read, and formula won't be calculating both parts anyway, so no extra calculations

=IF(COUNTA(D2:E2)=2,

SUMPRODUCT(($A$2:$A$11="Endowment")*($B$2:$B$11="Cape Town")*(($C$2:$C$11=D2)+($C$2:$C$11=E2))),

SUMPRODUCT(($A$2:$A$11="Endowment")*($B$2:$B$11="Cape Town")))
 
Luke M, that approach is so simple and elegant, yet so effective! It solves my issue perfectly!!


One day when I'm big, I also want to learn how to look beyond the trees and see the whole forest, so to speak!
 
Hi, Grumpy88!

Did you hear about Little Red Riding Hood? Remember the characters? Well I've never been sure if he was the wolf or the woodcutter...

Regards!

PS: And please don't put me in the commitment of having to guess.
 
In the immortal words of Rhett Butler: "Frankly my dear, I don't give a damn!"


I always thought that the wolf got a bum rap in Little Red Riding Hood anyway!
 
Glad I could help, and thanks for the humor. One of the many reasons why I love this site is the sense of community we have. =)
 
Also,


A small changes in your original formula.


=SUMPRODUCT(--($A$2:$A$11="Endowment"),--($B$2:$B$11="Cape Town"),SIGN(($C$2:$C$11=D2)+($C$2:$C$11=E2)+(COUNTA(D2,E2)<2)))
 
Nice trick Haseeb! Took me a little bit to figure out how the math was working out on that, but I like it. =)
 
You must log in or register to reply here.
Back
Top