Re: Counting with multiple matching criteria


I was thinking that SUMPRODUCT, being an array-processing function, would have imposed the array processing on the elements in the formula.

--
Rick (MVP - Excel)


"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message news:OKPG1gO9JHA.5780@xxxxxxxxxxxxxxxxxxxxxxx
>=SUM(--(G$1:G$100=TRANSPOSE(J1:J4)))
I found it interesting that using SUMPRODUCT instead
of SUM did not work correctly unless you array-entered it.

TRANSPOSE requires array entry.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message news:%23XW$xDO9JHA.1376@xxxxxxxxxxxxxxxxxxxxxxx
I'm assuming that there are more entry possibilities than the four you listed and that she wants to count only those four items from among all the possible entries (otherwise a simple COUNTA function call would work). Using the concept Shane posted, but modifying it for the search items to be listed in a column (J1:J4 in my formula) rather than a row (W1:Z1 in Shane's formula), this array-entered formula should work...

=SUM(--(G$1:G$100=TRANSPOSE(J1:J4)))

I found it interesting that using SUMPRODUCT instead of SUM did not work correctly unless you array-entered it.

--
Rick (MVP - Excel)


"Gary''s Student" <GarysStudent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:045D042B-1175-4331-B0D7-A3213A558961@xxxxxxxxxxxxxxxx
A colleague came to me yesterday and complained about the length of formulas.
She needs to count occurrences of values in a table that meet any of several
criteria. The table is pure text with no blanks. Her formula was something
like:

=COUNTIF(G7:G3147,"open")+COUNTIF(G7:G3147,"pending
review")+COUNTIF(G7:G3147,"review complete")+COUNTIF(G7:G3147,"assigned")

I pointed out that she did not need repeated COUNTIF()'s and to use:

=SUM(COUNTIF(G7:G3147,{"open","pending review","review complete","assigned"}))

She was satisfied, but returned this morning and wanted to know if the
criteria could be completely removed from the formula and stored in a table.

I put the match values in Z1 thru Z4 and tried:

=SUM(COUNTIF(G7:G3147,Z1:Z4)) but this returns zero.

Any suggestions for putting the criteria in a little table and referring to
that table??

--
Gary''s Student - gsnu200858




.

Relevant Pages

  • Re: sum last rows
    ... Microsoft MVP Excel ... Rick ... Change my 'Data Sheet' to the name of your sheet with the columns you are adding ...
    (microsoft.public.excel.programming)
  • Re: Saving a file to desktop with VBA
    ... Rick (MVP - Excel) ... Windows, although I don't know that for sure as I do not do any ...
    (microsoft.public.excel.programming)
  • Re: Testing
    ... but I couldn't get a response through to your newsgroup thread. ... Rick (MVP - Excel) ...
    (microsoft.public.excel.misc)
  • Re: assign a value to a cell
    ... Rick ... Microsoft MVP Excel ... when I type something in the cell it will stay eg. ...
    (microsoft.public.excel.newusers)
  • Re: Find value in a column and insert rows above
    ... Rick (MVP - Excel) ... Sub InsertY() ...
    (microsoft.public.excel.programming)