How do you count the number of times specific criteria is met in Column A,
and different criteria is met in Column B.
For example, Column A has numbers 1100, 2200, 3300 all formated as numbers.
Column B is formatted as and has dates, but some cells in Column B will be
blank.
How would you count the number of times 1100 appears in Column A when there
is date in Column B?

try this, changing columns to suit
=SUMPRODUCT((F2:F12=1100)*(LEN(TRIM(G2:G12))0))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Caddie66" wrote in message
...
How do you count the number of times specific criteria is met in Column A,
and different criteria is met in Column B.
For example, Column A has numbers 1100, 2200, 3300 all formated as
numbers.
Column B is formatted as and has dates, but some cells in Column B will be
blank.
How would you count the number of times 1100 appears in Column A when
there
is date in Column B?

It seemed to work with counting the instances for 1100, but it did not return
the correct a result for 2200 and did not return any result for 3300. The
answers should have been
1100 = 2
2200 = 2
3300 = 3

instead I get
1100 = 2
2200 = 1
3300 = 0


"Don Guillett" wrote:

try this, changing columns to suit
=SUMPRODUCT((F2:F12=1100)*(LEN(TRIM(G2:G12))0))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Caddie66" wrote in message
...
How do you count the number of times specific criteria is met in Column A,
and different criteria is met in Column B.
For example, Column A has numbers 1100, 2200, 3300 all formated as
numbers.
Column B is formatted as and has dates, but some cells in Column B will be
blank.
How would you count the number of times 1100 appears in Column A when
there
is date in Column B?

try this
assume that col B always contain a date or blanks
adjust the column to yours

=SUMPRODUCT((A1:A6=1100)*(B1:B6<""))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Caddie66" wrote:

How do you count the number of times specific criteria is met in Column A,
and different criteria is met in Column B.
For example, Column A has numbers 1100, 2200, 3300 all formated as numbers.
Column B is formatted as and has dates, but some cells in Column B will be
blank.
How would you count the number of times 1100 appears in Column A when there
is date in Column B?

"Don Guillett" wrote...
try this, changing columns to suit
=SUMPRODUCT((F2:F12=1100)*(LEN(TRIM(G2:G12))0) )
....

Doesn't actually check for dates, just for either nonwhitespace text
or numbers in the second column range.

If the OP really means blank, then it'd be better to use either

=SUMPRODUCT((F2:F12=1100)*ISNUMBER(G2:G12))

or (more exacting by ensuring the number is a valid 1900-basis date
serial number)

=SUMPRODUCT((F2:F12=1100)*(G2:G12=--"1990-01-01")*
(G2:G12<=--"9999-12-31"))

But the OP's follow-up leads me to suspect the OP has a mixture of
text and numbers in the first column range, so more robust to use

=SUMPRODUCT((--F2:F12=1100)*(G2:G12=--"1990-01-01")*
(G2:G12<=--"9999-12-31"))

We assumed with a name like Francis that you're not greek. Did you want to
say "geek" instead?

Regards,
Fred.

"Francis" <xlsmate(AT)gmail(DOT)com wrote in message
...
try this
assume that col B always contain a date or blanks
adjust the column to yours

=SUMPRODUCT((A1:A6=1100)*(B1:B6<""))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Caddie66" wrote:

How do you count the number of times specific criteria is met in Column
A,
and different criteria is met in Column B.
For example, Column A has numbers 1100, 2200, 3300 all formated as
numbers.
Column B is formatted as and has dates, but some cells in Column B will
be
blank.
How would you count the number of times 1100 appears in Column A when
there
is date in Column B?

hi - I eventually used a combination of our formulas and others on this
website. This is the formula that worked:

=SUMPRODUCT(--(A10:A169=A33),--(B10:B1690))

I changed the "=1100" to =A33, which is of the cells that contains 1100.

Then there were (oddly) some data normalization issues (not all the cells
with 1100 and were formatted the same were the same to Excel, so I copied and
pasted to avoid the issue in the formula.)

Column B, above, is the column with date entries.

Many thanks to all who sent replies to help-out!

"Harlan Grove" wrote:

"Don Guillett" wrote...
try this, changing columns to suit
=SUMPRODUCT((F2:F12=1100)*(LEN(TRIM(G2:G12))0) )
....

Doesn't actually check for dates, just for either nonwhitespace text
or numbers in the second column range.

If the OP really means blank, then it'd be better to use either

=SUMPRODUCT((F2:F12=1100)*ISNUMBER(G2:G12))

or (more exacting by ensuring the number is a valid 1900-basis date
serial number)

=SUMPRODUCT((F2:F12=1100)*(G2:G12=--"1990-01-01")*
(G2:G12<=--"9999-12-31"))

But the OP's follow-up leads me to suspect the OP has a mixture of
text and numbers in the first column range, so more robust to use

=SUMPRODUCT((--F2:F12=1100)*(G2:G12=--"1990-01-01")*
(G2:G12<=--"9999-12-31"))