|
countifs
I need a formula that counts if column C = Sold, and column A = Name and
column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
One way:
=SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Ooops!
Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Using SUMPRODUCT()
=SUMPRODUCT(--(A2:A100="name"),--(C2:C100="sold"),--ISNUMBER(MATCH(s2:s100,{"red","black"},0))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Should be the below for wildcards...
=SUMPRODUCT((A2:A100="name")*(C2:C100="sold")*(ISN UMBER(SEARCH({"NEW","UNDER"},S2:S100)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
I am trying to get this to draw from another sheet and keep getting an error.
any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Works ok for me.
When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Trev; try copy paste the below (the last one was a single quote instead of
double quote which caused the error) =SUM(COUNTIFS('Pending Sold'!A1:A10,"name",'Pending Sold'!C1:C10,"sold",'Pending Sold'!S1:S10,{"new*","under*"})) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
OK, now I see why I got that error message.
....'Pending Sold'!S:S,{"new";"under'})) You're missing the second quote after under. ....'Pending Sold'!S:S,{"new";"under"})) I have poor eyesight. That's why I didn't catch it the first time around. One of these days I'm gonna have to bail out of these forums because of my poor eyesight. Either that or I'm gonna have to get a 52 inch monitor and view everything in a 40pt font size! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
this works when I put it on one sheet but I need it to look for the info on
another sheet. When I try =SUMPRODUCT(--('sheet'!A:A="name"),--('sheet'!C:C="sold"),--ISNUMBER(MATCH('aheet'!S:S,{"red","black"},0))) I receive a return of 0 "Jacob Skaria" wrote: Using SUMPRODUCT() =SUMPRODUCT(--(A2:A100="name"),--(C2:C100="sold"),--ISNUMBER(MATCH(s2:s100,{"red","black"},0))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Hi,
You have misspelled sheet in the last portion - change the spelling to sheet from aheet. Also, please avoid using entire columns as references -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Trev" wrote in message ... this works when I put it on one sheet but I need it to look for the info on another sheet. When I try =SUMPRODUCT(--('sheet'!A:A="name"),--('sheet'!C:C="sold"),--ISNUMBER(MATCH('aheet'!S:S,{"red","black"},0))) I receive a return of 0 "Jacob Skaria" wrote: Using SUMPRODUCT() =SUMPRODUCT(--(A2:A100="name"),--(C2:C100="sold"),--ISNUMBER(MATCH(s2:s100,{"red","black"},0))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Thanks everything works well!!!!!!!!!!!!!!
"T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Trev" wrote in message ... Thanks everything works well!!!!!!!!!!!!!! "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Now I need to take this formula and make it count a range.
=SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Try this:
=SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
This is returning a count of 0, should be 66. Any ideas. The last part does
not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Tre; try the below...(Take a close look at these formulas and see whats the
difference between those to understand how they work.) =SUMPRODUCT(--('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1), --('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S2:S100)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Correction; try the below
=SUMPRODUCT(--('Pending Sold'!A:A="name"),--('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S:S)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
There could be a myriad of reasons. See this:
http://contextures.com/xlFunctions02.html#Trouble Although the article is describing another function the problems being discussed apply to any and all functions. You can try replacing this: MATCH('Pending Sold'!S:S,{"new","under"},0) With this: SEARCH({"new","under"},'Pending Sold'!S:S) -- Biff Microsoft Excel MVP "Trev" wrote in message ... This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Dude!
How about exercising some "net etiquette". -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction; try the below =SUMPRODUCT(--('Pending Sold'!A:A="name"),--('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S:S)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
I am confused and my formula still does not work. Any help?
"T. Valko" wrote: Dude! How about exercising some "net etiquette". -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction; try the below =SUMPRODUCT(--('Pending Sold'!A:A="name"),--('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S:S)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
That comment was directed at Jacob.
Here's a small sample file that demonstrates this. xTrev.xlsx 8kb http://cjoint.com/?kkxj281z7M -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am confused and my formula still does not work. Any help? "T. Valko" wrote: Dude! How about exercising some "net etiquette". -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction; try the below =SUMPRODUCT(--('Pending Sold'!A:A="name"),--('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S:S)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Oops. I havn't looked at this question for a while; and thought Trevs last
response is unattended.. PS: From the original query onwards; Trev is looking at ColS with wildcards; which I dont think you have noticed...(until your last response..) "T. Valko" wrote: That comment was directed at Jacob. Here's a small sample file that demonstrates this. xTrev.xlsx 8kb http://cjoint.com/?kkxj281z7M -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am confused and my formula still does not work. Any help? "T. Valko" wrote: Dude! How about exercising some "net etiquette". -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction; try the below =SUMPRODUCT(--('Pending Sold'!A:A="name"),--('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S:S)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
countifs
Trev, are you able to sort out this; or do you have problems still?
=SUMPRODUCT( --('Pending Sold'!A:A="name"), --('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1), --('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S:S)))) PS: As Biff mentioned refer the entire column only if really needed. If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: I am confused and my formula still does not work. Any help? "T. Valko" wrote: Dude! How about exercising some "net etiquette". -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction; try the below =SUMPRODUCT(--('Pending Sold'!A:A="name"),--('Pending Sold'!C:C="sold"), --('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000), --(ISNUMBER(SEARCH({"NEW","UNDER"},S:S)))) If this post helps click Yes --------------- Jacob Skaria "Trev" wrote: This is returning a count of 0, should be 66. Any ideas. The last part does not seem to working correctly. "T. Valko" wrote: Try this: =SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending Sold'!F:F=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending Sold'!S:S,{"new","under"},0)))) Note: you should not reference *entire* columns using this formula unless you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the above formula you're calcualting over 4,000,000 cells! -- Biff Microsoft Excel MVP "Trev" wrote in message ... Now I need to take this formula and make it count a range. =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) I need to count column F on the Pending Sold sheet if it is = 1 and F<= 150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"})) Make sense, Can you Help? "T. Valko" wrote: Works ok for me. When I copied your posted formula and pasted it I got the general formula error message: The formula you typed contains an error.... It highlighted this portion of the formula: {"new";"under"})) I just backspaced that out and retyped and it worked. -- Biff Microsoft Excel MVP "Trev" wrote in message ... I am trying to get this to draw from another sheet and keep getting an error. any ideas? =SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'})) "T. Valko" wrote: Ooops! Used the wrong range for that last criteria. Should be: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{ "new";"under"})) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: =SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D2:D10,{ "new";"under"})) -- Biff Microsoft Excel MVP "Trev" wrote in message ... I need a formula that counts if column C = Sold, and column A = Name and column S and be either New* or Under* =COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under Is this possible? Thanks |
| All times are GMT +1. The time now is 02:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com