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,>
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,>
Last edited: