| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countifs | Excel Worksheet Functions | |||
| CountIfs | Excel Worksheet Functions | |||
| COUNTIFS with an OR? | Excel Worksheet Functions | |||
| Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
| countifs | Excel Discussion (Misc queries) | |||
![Trev[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
