would someone be able to tell me why this is not working?
Countif(("Dups"."Lead Type"='Life Insurance' and "Dups"."Phone")=("Dups"."Lead Type"='Health Insurance' and "Dups"."Phone"))
any help appreciated
would someone be able to tell me why this is not working?
Countif(("Dups"."Lead Type"='Life Insurance' and "Dups"."Phone")=("Dups"."Lead Type"='Health Insurance' and "Dups"."Phone"))
any help appreciated
bump
more bumps appreciate the help
anyone out there?
Just a cool count the dups if statement....
the language doesn't look familiar, but it still looks odd the way you've formatted that.
You're not bracketing properly, and I hardly ever see anything coding that uses "and" instead of &, or +, etc.
Countif(
("Dups"."Lead Type"='Life Insurance')
&
("Dups"."Phone"=?????)
&
("Dups"."Lead Type"='Health Insurance')
&
("Dups"."Phone"=?????)
)
See what I mean?
This is just excell formulation and or possibly database stuff. its an agregate formula that will count 1 if it find a phone number where it has two lead types (life insurance and health insurance)
Countif("Dups"."Phone" & (("Dups"."Lead Type"='Life Insurance') & ("Dups"."Lead Type"='Health Insurance')))
No, I don't excell. But no-one else is here so why not?
Besides... it bumps, which might bring someone in.
I think you have to put it in excel first
Been googling.
I'm naive, I admit. But it seems like you can't select ranges this way. You seem to need to specify the cells. So here's my next attempt:
IF(Countif(B2:B13,'Life Insurance')+Countif(B2:B13,'Health Insurance'))
>Countif("Dups"."Phone" & (("Dups"."Lead Type"='Life Insurance') & ("Dups"."Lead Type"='Health Insurance')))
sorry nope still getting 0 as a value when i try it out.
obviously, the range "B2:B13" is just an example. Modify as needed.
i think what i need to do is a count the phone number when it equal said lead type do it for both. then do a count if when that count if equal to other count....thanks for the help tried this to no avail.
Sigh. I'm fucking you all up here OP.
IF(Countif(B2:B13,"Life Insurance")+Countif(B2:B13,"Health Insurance"))
I used single quote instead of double.
Using IF as a wrapper should return 1 if either conditions are met, given the range.
Honestly, I know I'm probably shit for help.. but the bumps will bring someone who knows.
technically it worked but it only gave a value of 1 ha
the value would be 100s just from spot checking. theres 4382 rows
It doesn't look like the right formula.
Countif (range , condition)
Ex: Countif(B2 , "Apples") will count 1 if the cell contains "Apples".
Have you checked countifS?
=Countifs(RANGE1,"Life Insurance",RANGE2,"Health Insurance")
And I don't know your data.. how many "insurance" types do you have? If it's only those two...
=Countif(RANGE1,"* Insurance")
so do you think it would be better to break it down to two tables? where count if this range on this table = this range on another table?
this would be an example
email created time lead type phone
[email protected] 3/28/2016 16:18 On-Peak EXCL 9379999999
[email protected] 8/2/2016 8:02 Premium Connect 9379999999
not sure how that looks will check after post
to if each heading was a row. and i got the right if statement it would look at the phone numbers and one count 1 if it was looking on peak and premium connect. maybe it wont work this way not sure.
bumping while I look