If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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(A44,"A")0)*(COUNTIF(A44,"B")0) Then just get the SUM of the helper column. If you want a single "brute force" formula: =SUMPRODUCT(--(COUNTIF(OFFSET(A413,ROW(A413)-MIN(ROW(A413)),,1),"A")0),--(COUNTIF(OFFSET(A413,ROW(A413)-MIN(ROW(A413)),,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 |
#3
|
|||
|
|||
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(A44,"A")0)*(COUNTIF(A44,"B")0) Then just get the SUM of the helper column. If you want a single "brute force" formula: =SUMPRODUCT(--(COUNTIF(OFFSET(A413,ROW(A413)-MIN(ROW(A413)),,1),"A")0),--(COUNTIF(OFFSET(A413,ROW(A413)-MIN(ROW(A413)),,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 |
Thread Tools | |
Display Modes | |
|
|