Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
I have a chart 100 rows and 10 columns. Each row has 10 non duplicate
letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3 -poiuyhvcma and so forth. I need a formula to tell me how many rows have m and h. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
Relying on your statement each row has 10 *non-duplicate* letters and your
data is in A1:J100 (adjust range as needed), try this: =SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2)) array entered with Cntrl+Shift+Enter (or you'll get #VALUE). "ekim notpmah" wrote: I have a chart 100 rows and 10 columns. Each row has 10 non duplicate letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3 -poiuyhvcma and so forth. I need a formula to tell me how many rows have m and h. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
hi, jmb tried formula, got "formula contains error" ???????????? any ideas
if I am doing something wrong? Mike "JMB" wrote: Relying on your statement each row has 10 *non-duplicate* letters and your data is in A1:J100 (adjust range as needed), try this: =SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2)) array entered with Cntrl+Shift+Enter (or you'll get #VALUE). "ekim notpmah" wrote: I have a chart 100 rows and 10 columns. Each row has 10 non duplicate letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3 -poiuyhvcma and so forth. I need a formula to tell me how many rows have m and h. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
And if each row may contain one or more value of m or h, I would create a new
column to the right of the range (column k) with the formula in K1: =SIGN(COUNTIF(A1:J1,"m")*COUNTIF(A1:J1,"h")), and make the SUM(K1:K100) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
Stephane, that works but if I have to do a lot of calculations,that is a lot
of columns. Do you know if there is a sumproduct formula using frequency that can do this. I know that if the items are in given columns I can use =sumproduct(--(d1:di00="m"),--(ji:j100="h")) but how do you do it when the items are anywhere in the row??? Mike "Stephane Quenson" wrote: And if each row may contain one or more value of m or h, I would create a new column to the right of the range (column k) with the formula in K1: =SIGN(COUNTIF(A1:J1,"m")*COUNTIF(A1:J1,"h")), and make the SUM(K1:K100) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
Hard to say since you didn't post the formula you actually used. I copied it
straight from the NG into a worksheet and it worked. Here is a slightly shorter version of the same thing. It also relies on there not being any duplicates of m or h in the same row and assumes your data is in A1:J100: =SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))=2)) or if there could be duplicates, try this slightly longer version: =SUMPRODUCT(--(MMULT(--(A1:J100="m"),TRANSPOSE(--(COLUMN(A1:J1)0)))0),--(MMULT(--(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))0)) both array entered (Cntrl+Shift+Enter). Stephanes formula will work and will only requires one additional column. There may be a way to do it with sumproduct/frequency, but I don't know how to do it and not come up w/a formula that is longer/more complex than using MMULT (not to say it can't be done). I've tested all of the formulas I posted and they give me the correct answers (under the assumptions I've stated). If you still have issues, copy the formula you are using directly from the worksheet into your post. "ekim notpmah" wrote: hi, jmb tried formula, got "formula contains error" ???????????? any ideas if I am doing something wrong? Mike "JMB" wrote: Relying on your statement each row has 10 *non-duplicate* letters and your data is in A1:J100 (adjust range as needed), try this: =SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2)) array entered with Cntrl+Shift+Enter (or you'll get #VALUE). "ekim notpmah" wrote: I have a chart 100 rows and 10 columns. Each row has 10 non duplicate letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3 -poiuyhvcma and so forth. I need a formula to tell me how many rows have m and h. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
Then I would use a different approach, first by making a new column (only
one!) with the formula on cell K18: =A18&B18&C18&D18&E18&F18&G18&H18&I18&J18. As you can see, it makes the concatenation of all the cells on the row. Why CONCATENATE function does not work with a range is beyond my understanding, but we have to make with what we have, haven't we? Next, I will use the following array formula (validate with Shift-Ctrl-Enter to create curly brackets { } around the formula). It is using function FIND, which returns the position of a character in a string, or an error if not. So the formula is, if the range to use if K18:K19, =SUM(NOT(ISERROR(FIND("m",K18:K19,1)))*NOT(ISERROR (FIND("h",K18:K19,1)))) One remark about column K. It works based on your description, but in case "m" and "h" are more than one character, insert a separator like "-" between two cells to avoid a situation where the concatenation of two cells create a value that you do not want, like "zx" & "vy" would find "xv", while "zx" & "-" & "vy" will work properly and will never FIND "xv". |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting ROWS that have 2 criteria
jmb and stephane, I figured out how to use both of your formulas.JMB I like
yours better. Thanks a lot guys. I really appreciate it , Mike.In my searching i found a great site for explaining SUMPRODUCT xldynamic.com look under 'Multiple Condition tests -- Sumproduct. Good luck "JMB" wrote: Hard to say since you didn't post the formula you actually used. I copied it straight from the NG into a worksheet and it worked. Here is a slightly shorter version of the same thing. It also relies on there not being any duplicates of m or h in the same row and assumes your data is in A1:J100: =SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))=2)) or if there could be duplicates, try this slightly longer version: =SUMPRODUCT(--(MMULT(--(A1:J100="m"),TRANSPOSE(--(COLUMN(A1:J1)0)))0),--(MMULT(--(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))0)) both array entered (Cntrl+Shift+Enter). Stephanes formula will work and will only requires one additional column. There may be a way to do it with sumproduct/frequency, but I don't know how to do it and not come up w/a formula that is longer/more complex than using MMULT (not to say it can't be done). I've tested all of the formulas I posted and they give me the correct answers (under the assumptions I've stated). If you still have issues, copy the formula you are using directly from the worksheet into your post. "ekim notpmah" wrote: hi, jmb tried formula, got "formula contains error" ???????????? any ideas if I am doing something wrong? Mike "JMB" wrote: Relying on your statement each row has 10 *non-duplicate* letters and your data is in A1:J100 (adjust range as needed), try this: =SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2)) array entered with Cntrl+Shift+Enter (or you'll get #VALUE). "ekim notpmah" wrote: I have a chart 100 rows and 10 columns. Each row has 10 non duplicate letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3 -poiuyhvcma and so forth. I need a formula to tell me how many rows have m and h. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |