Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct or countif
Is there a way to use the sumproduct function to get the same result as the
following: countif (range, "*text*") I'm using the sumproduct to do a count based on 2 criteria being true in 2 separate columns, but I can't guarantee that the text I';m looking for (BDS) will always be at the beginning of the text string of the second column. It may be in the middle, at the end, or the beginning. Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct or countif
maybe:
=SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse")) mpierre wrote: Is there a way to use the sumproduct function to get the same result as the following: countif (range, "*text*") I'm using the sumproduct to do a count based on 2 criteria being true in 2 separate columns, but I can't guarantee that the text I';m looking for (BDS) will always be at the beginning of the text string of the second column. It may be in the middle, at the end, or the beginning. Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct or countif
If you want it case sensitive, change the SEARCH to FIND
=SUMPRODUCT(--(--ISNUMBER(FIND("bds",$A$1:$A$10))),--(B1:B10="somethingelse" )) -- HTH RP "Dave Peterson" wrote in message ... maybe: =SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse")) mpierre wrote: Is there a way to use the sumproduct function to get the same result as the following: countif (range, "*text*") I'm using the sumproduct to do a count based on 2 criteria being true in 2 separate columns, but I can't guarantee that the text I';m looking for (BDS) will always be at the beginning of the text string of the second column. It may be in the middle, at the end, or the beginning. Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct or countif
And maybe change the lower case bds to BDS (to match the original post)--or even
(BDS). Bob Phillips wrote: If you want it case sensitive, change the SEARCH to FIND =SUMPRODUCT(--(--ISNUMBER(FIND("bds",$A$1:$A$10))),--(B1:B10="somethingelse" )) -- HTH RP "Dave Peterson" wrote in message ... maybe: =SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse")) mpierre wrote: Is there a way to use the sumproduct function to get the same result as the following: countif (range, "*text*") I'm using the sumproduct to do a count based on 2 criteria being true in 2 separate columns, but I can't guarantee that the text I';m looking for (BDS) will always be at the beginning of the text string of the second column. It may be in the middle, at the end, or the beginning. Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct or countif
This worked great. THANK you very much.
"Dave Peterson" wrote: maybe: =SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse")) mpierre wrote: Is there a way to use the sumproduct function to get the same result as the following: countif (range, "*text*") I'm using the sumproduct to do a count based on 2 criteria being true in 2 separate columns, but I can't guarantee that the text I';m looking for (BDS) will always be at the beginning of the text string of the second column. It may be in the middle, at the end, or the beginning. Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF, SUMPRODUCT, or something else | New Users to Excel | |||
Countif Sumproduct | Excel Worksheet Functions | |||
Countif or Sumproduct | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMPRODUCT or COUNTIF? | Excel Worksheet Functions |