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
|
|||
|
|||
COUNTIF ON 2 VARIABLES ??
Thanks for all the previous assistance... however I have now encountered
another couple of problems Again back to my large spreadsheet issues ... I have several colums of which I would like to count the reoccurence of a certain value 1 column comprises of a potentially random digit between 1 and 400 ... the other being either 1 or 0 ... I am looking for a formula that incrementally counts every time both values match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345, a count of 1 for 232 and 0 for every other value inbetween... I assume the vb code would be something like If A = 345 and B = 1 then Count However can get my head round a suitable formula Many thanks Alan |
#2
|
|||
|
|||
Assumning your data in columns a & b for 27 rows
=SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1) Lance "Alan" wrote: Thanks for all the previous assistance... however I have now encountered another couple of problems Again back to my large spreadsheet issues ... I have several colums of which I would like to count the reoccurence of a certain value 1 column comprises of a potentially random digit between 1 and 400 ... the other being either 1 or 0 ... I am looking for a formula that incrementally counts every time both values match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345, a count of 1 for 232 and 0 for every other value inbetween... I assume the vb code would be something like If A = 345 and B = 1 then Count However can get my head round a suitable formula Many thanks Alan |
#3
|
|||
|
|||
Thanks Lance
However that appears to count all the appearances of the data value in column A not just those that have a 1 in column B ... I was looking for a method to total the number of instances of a value in column A where column B of the same row was 1 ... however no count is made if column b = 0 "LanceB" wrote: Assumning your data in columns a & b for 27 rows =SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1) Lance "Alan" wrote: Thanks for all the previous assistance... however I have now encountered another couple of problems Again back to my large spreadsheet issues ... I have several colums of which I would like to count the reoccurence of a certain value 1 column comprises of a potentially random digit between 1 and 400 ... the other being either 1 or 0 ... I am looking for a formula that incrementally counts every time both values match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345, a count of 1 for 232 and 0 for every other value inbetween... I assume the vb code would be something like If A = 345 and B = 1 then Count However can get my head round a suitable formula Many thanks Alan |
#4
|
|||
|
|||
Sorry
just change the (B1:B27=B1) to (B1:B27=1) Lance "Alan" wrote: Thanks Lance However that appears to count all the appearances of the data value in column A not just those that have a 1 in column B ... I was looking for a method to total the number of instances of a value in column A where column B of the same row was 1 ... however no count is made if column b = 0 "LanceB" wrote: Assumning your data in columns a & b for 27 rows =SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1) Lance "Alan" wrote: Thanks for all the previous assistance... however I have now encountered another couple of problems Again back to my large spreadsheet issues ... I have several colums of which I would like to count the reoccurence of a certain value 1 column comprises of a potentially random digit between 1 and 400 ... the other being either 1 or 0 ... I am looking for a formula that incrementally counts every time both values match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345, a count of 1 for 232 and 0 for every other value inbetween... I assume the vb code would be something like If A = 345 and B = 1 then Count However can get my head round a suitable formula Many thanks Alan |
#5
|
|||
|
|||
=SUMPRODUCT(-($A$1:$A$27=$A1),--($B$1:$B$27=1))
-- HTH RP (remove nothere from the email address if mailing direct) "Alan" wrote in message ... Thanks Lance However that appears to count all the appearances of the data value in column A not just those that have a 1 in column B ... I was looking for a method to total the number of instances of a value in column A where column B of the same row was 1 ... however no count is made if column b = 0 "LanceB" wrote: Assumning your data in columns a & b for 27 rows =SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1) Lance "Alan" wrote: Thanks for all the previous assistance... however I have now encountered another couple of problems Again back to my large spreadsheet issues ... I have several colums of which I would like to count the reoccurence of a certain value 1 column comprises of a potentially random digit between 1 and 400 ... the other being either 1 or 0 ... I am looking for a formula that incrementally counts every time both values match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345, a count of 1 for 232 and 0 for every other value inbetween... I assume the vb code would be something like If A = 345 and B = 1 then Count However can get my head round a suitable formula Many thanks Alan |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
global variables | Reiner Harmgardt | General Discussion | 3 | January 7th, 2005 03:13 PM |
My variables keep getting reset... | ~Jenny | General Discussion | 0 | August 23rd, 2004 02:45 PM |
countif for multiple variables | dwiener | Worksheet Functions | 1 | April 27th, 2004 02:43 PM |
Pb on Countif | Paul | Worksheet Functions | 0 | December 23rd, 2003 11:35 AM |