Countifs where a column doe not equal x

[magpie2000k]

Hi All I have read and read and tried and tried but cant get formula to work.

Basically the below is the written formula

=COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>Voided")

It all works but I need to exclude from the count Anywhere in column DATA!J:J where the entry is Voided or Possible Void

The formula when you remove that last criteria works and returns the correct number in the count but when I add the last criteria it returns 0 which is incorrect.

Please help

 

[Momentman]

If I go down to row 74 =COUNTIF(Data!J2:J74,"<>*Void*") it returns 66 which is correct

<tbody>
</tbody>

COUNTIFS functions as AND rather than OR, since the COUNTIF formula alone returns the right answer, then its possible there are no cells that meet ALL the conditions in the COUNTIFS #Just thinking

 

[magpie2000k]

They are simply Data H:H is column with sales persons name
All dates B3 is the name I want to find in column
Data I:I is Lead source
All Dates C2 is the lead source I want to select from the column
Data F:F is the month of the lead column
Month select 1 is the month that I need to select from the column.

I cant see anything wrong with it and it returns the correct result until I add the Void bit.

 

[magpie2000k]

No that is not the case
the return gives my the number of leads from a particular source for a particular sales person in a particular month. 0 would assume that all for every person have been voids

This is definitely not the case

 

[Aladin Akyurek]

Have a look at your entries whether they contain any stray spaces around...

 

[magpie2000k]

I have looked it is crazy. I checked but nothing.
If there were spaces in the other criteria other than voids it would fail before adding voids

and if status column had spaces it wouldnt return the correct result when I run just that formula....

but as I said I have also checked

would It help if I emailed you it?

 

[Aladin Akyurek]

I have looked it is crazy. I checked but nothing.
If there were spaces in the other criteria other than voids it would fail before adding voids

and if status column had spaces it wouldnt return the correct result when I run just that formula....

but as I said I have also checked

would It help if I emailed you it?

You could post 5 rows from each relevant range and state what the outcome would be...

 

[magpie2000k]

You could post 5 rows from each relevant range and state what the outcome would be...

Opportunity No.	Type of business	Business Sub Category	Opportunity Date	Month	Estimated Total	SalesPerson	OpportunityReason	Opportunity Status	Modified By	Modify Date	Next Contact date
TCO62197	Private Company	SME Misc	12/08/2014 12:59	Aug	1580	Christiann Pawson	Internet/Google Phone	Quote Created	Christiann Pawson	12/08/2014 13:11	22/08/2014 10:00
TCO62196	Individual	Commercial	12/08/2014 12:52	Aug	30	Michael Milham	Internet/Google Phone	Sale Completed	Michael Milham	12/08/2014 13:11	Sales Tab
TCO62195	Private Company	12/08/2014 12:49	Aug	0	admin	Internet/Google Phone	New	admin	12/08/2014 12:49
TCO62194	Private Company	Construction	12/08/2014 12:47	Aug	590	Josh Emeny	Internet/Google Phone	Quote Created	Josh Emeny	12/08/2014 12:53	12/08/2014 13:53
TCO62193	Private Company	SME Misc	12/08/2014 12:42	Aug	790	Del Padda	Internet/Google Phone	Quote Created	Del Padda	12/08/2014 12:59	12/08/2014 14:00

<colgroup><col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:9508;width:195pt" width="260"> <col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:10898;width:224pt" width="298"> </colgroup><tbody>
</tbody>

Trying to select where month is = to a set month
Sales person is = to specisied person
Opportunity reason is a specified reason
and opp status is not Void or possible void

Hope this is clear

 

[Aladin Akyurek]

What is the outcome you want to see?

 

[magpie2000k]

What is the outcome you want to see?

Thanks so much for your help and I feel really stupid..

I hadnt $$ on one formulat so it was referencing a blank cell.

Idiot

 

[Aladin Akyurek]

Thanks so much for your help and I feel really stupid..

I hadnt $$ on one formulat so it was referencing a blank cell.

Idiot

So everything is OK. That's great...