Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiple Criteria using countif
I'm trying to count the number of records in one worksheet and return the
results in a different workbook. The data is held in two different columns. I nned to search for all cells where the value is "m" or "c" in D1:D60, whilst column A1:A60 should contain "*Work*". Sorry if I'm not explaining myself properly, I'm new to this! Any help greatly appreciated. |
#2
|
|||
|
|||
Multiple Criteria using countif
One way
=SUMPRODUCT((ISNUMBER(SEARCH("work",A1:A60)))*((D1 :D60={"m","c"}))) -- Regards, Peo Sjoblom "calmo" wrote in message ... I'm trying to count the number of records in one worksheet and return the results in a different workbook. The data is held in two different columns. I nned to search for all cells where the value is "m" or "c" in D1:D60, whilst column A1:A60 should contain "*Work*". Sorry if I'm not explaining myself properly, I'm new to this! Any help greatly appreciated. |
#3
|
|||
|
|||
Multiple Criteria using countif
=SUMPRODUCT(--(ISNUMBER(FIND("Work",A1:A60))),--(ISNUMBER(MATCH(D1:D60,{"c",
"m"},0)))) -- HTH RP (remove nothere from the email address if mailing direct) "calmo" wrote in message ... I'm trying to count the number of records in one worksheet and return the results in a different workbook. The data is held in two different columns. I nned to search for all cells where the value is "m" or "c" in D1:D60, whilst column A1:A60 should contain "*Work*". Sorry if I'm not explaining myself properly, I'm new to this! Any help greatly appreciated. |
#4
|
|||
|
|||
Multiple Criteria using countif
I'm assuming that the asterisks around "*Work*" indicate that there may be
characters before and/or after the string in the cell. Start by entering this in the sheet containing the data: =SUMPRODUCT((ISNUMBER(SEARCH("work",A1:A60)))*(D1: D60={"m","c"})) Now, open the *other* WB, and copy this formula to it, which will allow XL to create the proper path for you. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "calmo" wrote in message ... I'm trying to count the number of records in one worksheet and return the results in a different workbook. The data is held in two different columns. I nned to search for all cells where the value is "m" or "c" in D1:D60, whilst column A1:A60 should contain "*Work*". Sorry if I'm not explaining myself properly, I'm new to this! Any help greatly appreciated. |
#5
|
|||
|
|||
Multiple Criteria using countif
This was perfect, thanks for your help.
"RagDyeR" wrote: I'm assuming that the asterisks around "*Work*" indicate that there may be characters before and/or after the string in the cell. Start by entering this in the sheet containing the data: =SUMPRODUCT((ISNUMBER(SEARCH("work",A1:A60)))*(D1: D60={"m","c"})) Now, open the *other* WB, and copy this formula to it, which will allow XL to create the proper path for you. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "calmo" wrote in message ... I'm trying to count the number of records in one worksheet and return the results in a different workbook. The data is held in two different columns. I nned to search for all cells where the value is "m" or "c" in D1:D60, whilst column A1:A60 should contain "*Work*". Sorry if I'm not explaining myself properly, I'm new to this! Any help greatly appreciated. |
#6
|
|||
|
|||
Multiple Criteria using countif
RagDyeR, will you give mine a shot?
I am trying to figure out a countif formula. Here is the explanation: Worksheet one (my data dump page): In column A I have store names listed. In column B I have the amount of each sale for that store. Worksheet two: In column A I have the store names listed. In column B I would like to have a count of $0.00 sales made for each store. The data on the first sheet shows $0.00 sales as 0. Thanks for the help. "RagDyeR" wrote: I'm assuming that the asterisks around "*Work*" indicate that there may be characters before and/or after the string in the cell. Start by entering this in the sheet containing the data: =SUMPRODUCT((ISNUMBER(SEARCH("work",A1:A60)))*(D1: D60={"m","c"})) Now, open the *other* WB, and copy this formula to it, which will allow XL to create the proper path for you. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "calmo" wrote in message ... I'm trying to count the number of records in one worksheet and return the results in a different workbook. The data is held in two different columns. I nned to search for all cells where the value is "m" or "c" in D1:D60, whilst column A1:A60 should contain "*Work*". Sorry if I'm not explaining myself properly, I'm new to this! Any help greatly appreciated. |
#7
|
|||
|
|||
Multiple Criteria using countif
=SUMIF(Sheet1!$A:$A,A1,Sheet1!$B$B)
-- HTH RP (remove nothere from the email address if mailing direct) "Jim" wrote in message ... RagDyeR, will you give mine a shot? I am trying to figure out a countif formula. Here is the explanation: Worksheet one (my data dump page): In column A I have store names listed. In column B I have the amount of each sale for that store. Worksheet two: In column A I have the store names listed. In column B I would like to have a count of $0.00 sales made for each store. The data on the first sheet shows $0.00 sales as 0. Thanks for the help. "RagDyeR" wrote: I'm assuming that the asterisks around "*Work*" indicate that there may be characters before and/or after the string in the cell. Start by entering this in the sheet containing the data: =SUMPRODUCT((ISNUMBER(SEARCH("work",A1:A60)))*(D1: D60={"m","c"})) Now, open the *other* WB, and copy this formula to it, which will allow XL to create the proper path for you. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "calmo" wrote in message ... I'm trying to count the number of records in one worksheet and return the results in a different workbook. The data is held in two different columns. I nned to search for all cells where the value is "m" or "c" in D1:D60, whilst column A1:A60 should contain "*Work*". Sorry if I'm not explaining myself properly, I'm new to this! Any help greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
countif function with multiple criteria | Excel Discussion (Misc queries) | |||
Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |