Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and COUNTIF Function Problems
I have client names in column A and the letters d, hh, h, or db in column B
to designate their status. There are multiple occurences for some clients in the spreadsheet. I want a formula that will count a letter once per client like the SUMIF function, not once for each occurence of the letter like the COUNTIF function. Let me know if someone needs clarification or has an answer to my dilemma. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and COUNTIF Function Problems
Are you wanting a count of how many times a specifc letter appears with a
specific name, or just how many letters? The first: =SUMPRODUCT(($A$1:$A$100="ClientName")*($B$1:$B$10 0="d")) The latter: =SUMPRODUCT(($A$1:$A$100="ClientName")*1) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Cassie" wrote: I have client names in column A and the letters d, hh, h, or db in column B to designate their status. There are multiple occurences for some clients in the spreadsheet. I want a formula that will count a letter once per client like the SUMIF function, not once for each occurence of the letter like the COUNTIF function. Let me know if someone needs clarification or has an answer to my dilemma. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and COUNTIF Function Problems
Maybe it would help if I showed an example:
A B 1 doe, john d 2 smith, mary d 3 doe, jane d 4 jolie, angelina d 5 pitt, brad d 6 sinatra, frank db 7 aniston, jennifer d 8 depp, johnny d 9 doe, john d I have a SUMIF function to count each client once, and another to count every person on the list for each occurence (i.e. the SUMIF counts John Doe once, but the other function counts him twice to count each time spent with him). Now I need a function that will count his hearing status once. The function I am using is counting his status twice, and I need it only once. Does this help more? "Luke M" wrote: Are you wanting a count of how many times a specifc letter appears with a specific name, or just how many letters? The first: =SUMPRODUCT(($A$1:$A$100="ClientName")*($B$1:$B$10 0="d")) The latter: =SUMPRODUCT(($A$1:$A$100="ClientName")*1) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Cassie" wrote: I have client names in column A and the letters d, hh, h, or db in column B to designate their status. There are multiple occurences for some clients in the spreadsheet. I want a formula that will count a letter once per client like the SUMIF function, not once for each occurence of the letter like the COUNTIF function. Let me know if someone needs clarification or has an answer to my dilemma. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and COUNTIF Function Problems
I'm afraid I don't understand. When would the formula give a value greater
than 1? (example?) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Cassie" wrote: Maybe it would help if I showed an example: A B 1 doe, john d 2 smith, mary d 3 doe, jane d 4 jolie, angelina d 5 pitt, brad d 6 sinatra, frank db 7 aniston, jennifer d 8 depp, johnny d 9 doe, john d I have a SUMIF function to count each client once, and another to count every person on the list for each occurence (i.e. the SUMIF counts John Doe once, but the other function counts him twice to count each time spent with him). Now I need a function that will count his hearing status once. The function I am using is counting his status twice, and I need it only once. Does this help more? "Luke M" wrote: Are you wanting a count of how many times a specifc letter appears with a specific name, or just how many letters? The first: =SUMPRODUCT(($A$1:$A$100="ClientName")*($B$1:$B$10 0="d")) The latter: =SUMPRODUCT(($A$1:$A$100="ClientName")*1) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Cassie" wrote: I have client names in column A and the letters d, hh, h, or db in column B to designate their status. There are multiple occurences for some clients in the spreadsheet. I want a formula that will count a letter once per client like the SUMIF function, not once for each occurence of the letter like the COUNTIF function. Let me know if someone needs clarification or has an answer to my dilemma. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with countif function | Excel Discussion (Misc queries) | |||
COUNTIF function within SUMIF | Excel Worksheet Functions | |||
SUMIF/COUNTIF problems | Excel Discussion (Misc queries) | |||
problems with sumif and countif | Excel Discussion (Misc queries) | |||
Problems with SUMIF function and Wildcards (* and ?) | Excel Worksheet Functions |