I'm using sumproduct to sort and count a database that meets certain
criteria. It was doing a great job until I needed to count a column with
variable criteria that I want to count.
=SUMPRODUCT((SORTFLE1!$B$2:$B$1000="Auto
Body")*(SORTFLE1!$D$2:$D$1000="Dodd")*(SORTFLE1!$G $2:$G$1000<"ED,13,504,VA")*(SORTFLE1!$H$2:$H$1000 ="AM"))
It would be easier if I could just say not count (ED,13,504,VA) and count
everything else that wasn't blank. Any suggestions?

--
STEVE A

=COUNTA(G2:G1000)-COUNTIF(G2:G1000,"ED,13,504,VA")


On Oct 22, 1:13*am, STEVE A wrote:
I'm using sumproduct to sort and count a database that meets certain
criteria. It was doing a great job until I needed to count a column with
variable criteria that I want to count.
=SUMPRODUCT((SORTFLE1!$B$2:$B$1000="Auto
Body")*(SORTFLE1!$D$2:$D$1000="Dodd")*(SORTFLE1!$G $2:$G$1000<"ED,13,504,VA")*(SORTFLE1!$H$2:$H$1000 ="AM"))
It would be easier if I could just say not count (ED,13,504,VA) and count
everything else that wasn't blank. Any suggestions?

--
STEVE A

Replace this:

(SORTFLE1!$G$2:$G$1000<"ED,13,504,VA")

With this:

(ISNA(MATCH(SORTFLE1!$G$2:$G$1000,{"ED",13,504,"VA "},0)))

Note: that will count empty cells.

--
Biff
Microsoft Excel MVP


"STEVE A" wrote in message
...
I'm using sumproduct to sort and count a database that meets certain
criteria. It was doing a great job until I needed to count a column with
variable criteria that I want to count.
=SUMPRODUCT((SORTFLE1!$B$2:$B$1000="Auto
Body")*(SORTFLE1!$D$2:$D$1000="Dodd")*(SORTFLE1!$G $2:$G$1000<"ED,13,504,VA")*(SORTFLE1!$H$2:$H$1000 ="AM"))
It would be easier if I could just say not count (ED,13,504,VA) and count
everything else that wasn't blank. Any suggestions?

--
STEVE A

Try replacing this term in your sumproduct expression:
(SORTFLE1!$G$2:$G$1000<"ED,13,504,VA")
with this:
(ISERROR(MATCH(SORTFLE1!$G$2:$G$1000,{"ED";13;504; "VA"},0)))*(SORTFLE1!$G$2:$G$1000<"")
Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"STEVE A" wrote:
I'm using sumproduct to sort and count a database that meets certain
criteria. It was doing a great job until I needed to count a column with
variable criteria that I want to count.
=SUMPRODUCT((SORTFLE1!$B$2:$B$1000="Auto
Body")*(SORTFLE1!$D$2:$D$1000="Dodd")*(SORTFLE1!$G $2:$G$1000<"ED,13,504,VA")*(SORTFLE1!$H$2:$H$1000 ="AM"))
It would be easier if I could just say not count (ED,13,504,VA) and count
everything else that wasn't blank. Any suggestions?