Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the occurrence of data in one column based on two criteri
I have a database built in Excel (about 3000 rows and 25 columns). The
database grows in rows daily. One of the columns (say D), lists street addresses, another (sayB), lists the nature of the callout to that address (three different callout reasons could occur). I have inserted a blank column and in this column I want to enter a formula (in each row) to give the number of times a callout has occurred for that address for its corresponding reason. With just one criteria, that of how many times a callout to an address has occurred the formula COUNTIF(D:D,Dline number) works. I am not sure how to bring in the second criteria €“ I have been trying SUMPRODUCT without success. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the occurrence of data in one column based on two criteri
Assuming no header rows enter this in the first row of the col you have
inserted =SUMPRODUCT(--(D:D=D1),--(B:B=B1)) In Excel 2003 replace D:D with D1:D3000 (if 3000 is the last row) and B:B with B1:B3000 "Jim Prentice" wrote: I have a database built in Excel (about 3000 rows and 25 columns). The database grows in rows daily. One of the columns (say D), lists street addresses, another (sayB), lists the nature of the callout to that address (three different callout reasons could occur). I have inserted a blank column and in this column I want to enter a formula (in each row) to give the number of times a callout has occurred for that address for its corresponding reason. With just one criteria, that of how many times a callout to an address has occurred the formula COUNTIF(D:D,Dline number) works. I am not sure how to bring in the second criteria €“ I have been trying SUMPRODUCT without success. Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the occurrence of data in one column based on two cri
"Per Jessen" wrote: Hi I think this is what you want: =SUMPRODUCT(--($D$1:$D$65535=D1),--($B$1:$B$65535=B1)) Regards, Per "Jim Prentice" <Jim skrev i meddelelsen ... I have a database built in Excel (about 3000 rows and 25 columns). The database grows in rows daily. One of the columns (say D), lists street addresses, another (sayB), lists the nature of the callout to that address (three different callout reasons could occur). I have inserted a blank column and in this column I want to enter a formula (in each row) to give the number of times a callout has occurred for that address for its corresponding reason. With just one criteria, that of how many times a callout to an address has occurred the formula COUNTIF(D:D,Dline number) works. I am not sure how to bring in the second criteria €“ I have been trying SUMPRODUCT without success. Any help would be greatly appreciated. Thank you to both Sheeloo and Per Jessen. The formula from Sheeloo gives a #NUM error while the one from Per Jessen works perfectly. Thanks very much I spent hours trying to get this right. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting items in one column based on criteria in another column | Excel Worksheet Functions | |||
Counting occurrence of day of week AND hour of day | Excel Discussion (Misc queries) | |||
Counting entries in column based on condition in another column | Excel Worksheet Functions | |||
Counting occurrences in one column based on an occurrence in anoth | Excel Worksheet Functions | |||
Vlookup based on designated occurrence of value | Excel Worksheet Functions |