count cells that meet 2 criteria

  • Thread starter Thread starter aimee
  • Start date Start date
A

aimee

What formula should I use to count how many cells in columns G,I,K,M contain
8-May and how many cells in columns H,J,L,N contain Passed (for example)?
 
=SUMPRODUCT((MOD(COLUMN(G2:M20)-COLUMN(G2),2)=0)*(G2:M20=--"2008-05-08")*(H2:N20="Passed"))
 
=SUMPRODUCT((MOD(COLUMN(G2:M20)-COLUMN(G2),2)=0)*(G2:M20=--"2008-05-08")*(H2:N20="Passed"))

--
__________________________________
HTH

Bob
Click to expand...

Hey, Bob. Can you explain what your first argument is doing in that
formula? I don't mean to hijack, but maybe this would help the OP, too.
 
Because the data is offset, the first column has one set of data, the second
has another, that first test is to do an '... every other column' test (note
how the second range is offset at start and end by one column.

Although it may be overkill, the simpler formula

=SUMPRODUCT((G2:M20=--"2008-05-08")*(H2:N20="Passed"))

could allow a situation whereby say H8 had a date of th May and I8 had
Passed to be counted, which against the OPs rules.
 
Back
Top