Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Checking on two values
To count the numbers of rows that include the number "16" in column "I", I
use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes |
#2
|
|||
|
|||
Hei Johannes
COUNTIF takes only one criteria. You can do this with =SUMPRODUCT((B1:B1000="A")*(I1:I1000=16)) på norsk heter den =SUMMERPRODUKT((B1:B1000="A")*(I1:I1000=16)) There are two things to this though: You need a spesified range of rows, here 1000 of them. And 16 is a number, "16" is text. SUMIF will include both, SUMPRODUCT will not. HTH. Best wishes Harald "Johannes B." skrev i melding ... To count the numbers of rows that include the number "16" in column "I", I use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes |
#3
|
|||
|
|||
One way:
In cell J1 insert the formula =A2&I2 and copy it down to end of data Then you can use =Countif(J:J,"A16") Hope this helds Rowan "Johannes B." wrote: To count the numbers of rows that include the number "16" in column "I", I use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes |
#4
|
|||
|
|||
Excelent, thanks!
"Harald Staff" wrote: Hei Johannes COUNTIF takes only one criteria. You can do this with =SUMPRODUCT((B1:B1000="A")*(I1:I1000=16)) på norsk heter den =SUMMERPRODUKT((B1:B1000="A")*(I1:I1000=16)) There are two things to this though: You need a spesified range of rows, here 1000 of them. And 16 is a number, "16" is text. SUMIF will include both, SUMPRODUCT will not. HTH. Best wishes Harald "Johannes B." skrev i melding ... To count the numbers of rows that include the number "16" in column "I", I use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes |
#5
|
|||
|
|||
"?B?Um93YW4=?=" wrote in message ...
One way: In cell J1 insert the formula =A2&I2 and copy it down to end of data Then you can use =Countif(J:J,"A16") Hope this helds Rowan "Johannes B." wrote: To count the numbers of rows that include the number "16" in column "I", I use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes Hi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
How do I summarize totals for a list of values in Excel? | Excel Worksheet Functions | |||
Checking ALL values in a range | Excel Discussion (Misc queries) | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Formula to list unique values | Excel Worksheet Functions |