Minor error in COUNTIFS

[Lanz]

I have a question using the COUNTIFS function in excel 2007. I have previously used excel 2003 and created array formulas for my queries, however to standardize a table and make it useful for new datasets (not having to re-describe ranges in columns) the COUNTIFS function proves much more useful by handling this.

My problem is this:
I have a COUNTIFS function that defines multiple criteria, but I cannot get the function to discount cells that are not a certain value represented by a cell.
Example,
This works, where cell D2 is the number 53 and formatted as a number.
=COUNTIFS(sheet1!$range:$range<wbr>,A6,sheet1!$range$range,$C$2,<wbr>sheet1!$range:$range,$D$2)

This does not work, but I want it to:
=COUNTIFS(sheet1!$range:$range<wbr>,A6,sheet1!$range:$range,$C$2,<wbr>sheet1!$range:$range,<>$D$2)

However, replacing the D2 cell with the direct value works, but is not what I want:
=COUNTIFS(sheet1!$range:$range<wbr>,A6,sheet1!$range:$range,$C$2,<wbr>sheet1!$range:$range,"<>53")

I have tried to define the range individually, such as making criteria >d2 and one <d2, but this also did not work. After some time trying this and eventually staring at Excels Ribbon, I am perplexed. If you can help this would be greatly appreciated.<d2, but="" also="" did="" not="" work.="" after="" some="" time="" trying="" other="" uses="" of="" quotes,="" and="" eventually="" staring="" at="" excels="" ribbon,="" i="" am="" perplexed.="" if="" you="" can="" help="" this="" would="" be="" greatly="" appreciated.=""><d2, but="" also="" did="" not="" work.="" after="" some="" time="" trying="" and="" eventually="" staring="" at="" excels="" ribbon,="" i="" am="" perplexed.="" if="" you="" can="" help="" this="" would="" be="" greatly="" appreciated.="">
Thanks</d2,></d2,>

 

[jeffreybrown]

Have you tried...

"<>"&$D$2

 

[JoeMo]

I have a question using the COUNTIFS function in excel 2007. I have previously used excel 2003 and created array formulas for my queries, however to standardize a table and make it useful for new datasets (not having to re-describe ranges in columns) the COUNTIFS function proves much more useful by handling this.

My problem is this:
I have a COUNTIFS function that defines multiple criteria, but I cannot get the function to discount cells that are not a certain value represented by a cell.
Example,
This works, where cell D2 is the number 53 and formatted as a number.
=COUNTIFS(sheet1!$range:$range<wbr>,A6,sheet1!$range$range,$C$2,<wbr>sheet1!$range:$range,$D$2)

This does not work, but I want it to:
=COUNTIFS(sheet1!$range:$range<wbr>,A6,sheet1!$range:$range,$C$2,<wbr>sheet1!$range:$range,<>$D$2)

However, replacing the D2 cell with the direct value works, but is not what I want:
=COUNTIFS(sheet1!$range:$range<wbr>,A6,sheet1!$range:$range,$C$2,<wbr>sheet1!$range:$range,"<>53")

I have tried to define the range individually, such as making criteria >d2 and one <d2, but="" also="" did="" not="" work.="" after="" some="" time="" trying="" and="" eventually="" staring="" at="" excels="" ribbon,="" i="" am="" perplexed.="" if="" you="" can="" help="" this="" would="" be="" greatly="" appreciated.=""><d2, but="" also="" did="" not="" work.="" after="" some="" time="" trying="" other="" uses="" of="" quotes,="" and="" eventually="" staring="" at="" excels="" ribbon,="" i="" am="" perplexed.="" if="" you="" can="" help="" this="" would="" be="" greatly="" appreciated.=""><d2, but="" also="" did="" not="" work.="" after="" some="" time="" trying="" and="" eventually="" staring="" at="" excels="" ribbon,="" i="" am="" perplexed.="" if="" you="" can="" help="" this="" would="" be="" greatly="" appreciated.="">
Thanks</d2,></d2,>

Try this:
</d2,>

=COUNTIFS(sheet1!$range:$range<wbr>,A6,sheet1!$range:$range,$C$2,<wbr>sheet1!$range:$range,"<>"&$D$2)

<d2, but="" also="" did="" not="" work.="" after="" some="" time="" trying="" and="" eventually="" staring="" at="" excels="" ribbon,="" i="" am="" perplexed.="" if="" you="" can="" help="" this="" would="" be="" greatly="" appreciated.="">
</d2,>

 

[Lanz]

That did it, it works. Thanks very much, I kind of wish I had asked earlier.

 

[Lanz]

I only wished I had asked earlier because I know nothing about using the ampersand sign, but will look into this now. Thank you Thank you.