another COUNTIF question
 

can the count if questions cont rows that have two different inputs in
different columns?

I need to count rows in which any of the columns in that row contain two
distinct values. So basically i need code this, Count the number or rows
that contain A AND B, in any of its columns.

Is that possible?

Thanks,

-Adam

another COUNTIF question
 

T. Valko/Biff,

Would you tell me please what the double dashes (--) mean?
--
Sociopath


"T. Valko" wrote:

If this is related to your other post where you say you have 1000's of rows
of data take the easy way:

Use a helper column and enter a formula like this on each row then copy down
to the last row:

=(COUNTIF(A4:D4,"A")0)*(COUNTIF(A4:D4,"B")0)

Then just get the SUM of the helper column.

If you want a single "brute force" formula:

=SUMPRODUCT(--(COUNTIF(OFFSET(A4:D13,ROW(A4:D13)-MIN(ROW(A4:D13)),,1),"A")0),--(COUNTIF(OFFSET(A4:D13,ROW(A4:D13)-MIN(ROW(A4:D13)),,1),"B")0))

--
Biff
Microsoft Excel MVP


"Adam" wrote in message
...
can the count if questions cont rows that have two different inputs in
different columns?

I need to count rows in which any of the columns in that row contain two
distinct values. So basically i need code this, Count the number or rows
that contain A AND B, in any of its columns.

Is that possible?

Thanks,

-Adam

another COUNTIF question
 

That is the "double unary minus" about which you were asking in another
thread.
--
David Biddulph

"SwearBear" wrote in message
...
T. Valko/Biff,

Would you tell me please what the double dashes (--) mean?
--
Sociopath


"T. Valko" wrote:

If this is related to your other post where you say you have 1000's of
rows
of data take the easy way:

Use a helper column and enter a formula like this on each row then copy
down
to the last row:

=(COUNTIF(A4:D4,"A")0)*(COUNTIF(A4:D4,"B")0)

Then just get the SUM of the helper column.

If you want a single "brute force" formula:

=SUMPRODUCT(--(COUNTIF(OFFSET(A4:D13,ROW(A4:D13)-MIN(ROW(A4:D13)),,1),"A")0),--(COUNTIF(OFFSET(A4:D13,ROW(A4:D13)-MIN(ROW(A4:D13)),,1),"B")0))

--
Biff
Microsoft Excel MVP


"Adam" wrote in message
...
can the count if questions cont rows that have two different inputs in
different columns?

I need to count rows in which any of the columns in that row contain
two
distinct values. So basically i need code this, Count the number or
rows
that contain A AND B, in any of its columns.

Is that possible?

Thanks,

-Adam