Home |
Search |
Today's Posts |
#1
|
|||
|
|||
count if function with if statements
=IF(D100,COUNTif('Transaction Data'!$C:$C,$B10,'Transaction
Data'!O:O),"0"),0) This formula won't work. I am getting a message, "too many arguments in the function". I need this formula to work when the total in the first worksheets is 0, then to count # of transaction in the separate worksheet that are 0 (not all transactions). In addition, I need to count the # of transactions 0 only for a specific range determined by $B10 from the first worksheet. Can you help? Thank you. Natalia |
#2
|
|||
|
|||
You need to read in help how countif works
countif(range,criteria) =IF(D100,SUMPRODUCT(--('Transaction Data'!$C1:$C1000=B10),--('Transaction Data'!$O1:$O10000)),0) will work, note that you cannot use the full range as in C:C but the above will count entries where C1:C1000 equals B10 and O1:O1000 0 -- Regards, Peo Sjoblom "Natalia" wrote in message ... =IF(D100,COUNTif('Transaction Data'!$C:$C,$B10,'Transaction Data'!O:O),"0"),0) This formula won't work. I am getting a message, "too many arguments in the function". I need this formula to work when the total in the first worksheets is 0, then to count # of transaction in the separate worksheet that are 0 (not all transactions). In addition, I need to count the # of transactions 0 only for a specific range determined by $B10 from the first worksheet. Can you help? Thank you. Natalia |
#3
|
|||
|
|||
Hi, Thank you for your response. The formula works...no error message, but it
does not return the correct values, it returns 0, which is not true. Are you sure that you need to use SumProduct and not Countif function? I have another formula =IF(SUMIF('Transaction Data'!$C:$C,$B10,'Transaction Data'!P:P)<0.05*$H$1,0,SUMIF('Transaction Data'!$C:$C,$B10,'Transaction Data'!N:N)) and it is working and you don't have to determine the range limits. I thought I will use this formula and use the Countif instead of Sumif with some modification and that should work fine. Any idea how I should change the above formual to work with Countif? Again, it works with Sumif and not I need instead of the sum, count the number of transactions that are 0. Natalia "Natalia" wrote: =IF(D100,COUNTif('Transaction Data'!$C:$C,$B10,'Transaction Data'!O:O),"0"),0) This formula won't work. I am getting a message, "too many arguments in the function". I need this formula to work when the total in the first worksheets is 0, then to count # of transaction in the separate worksheet that are 0 (not all transactions). In addition, I need to count the # of transactions 0 only for a specific range determined by $B10 from the first worksheet. Can you help? Thank you. Natalia |
#4
|
|||
|
|||
Thank you Peo. Your answer is great, I read it wrong.
Regards, Natalia "Peo Sjoblom" wrote: You need to read in help how countif works countif(range,criteria) =IF(D100,SUMPRODUCT(--('Transaction Data'!$C1:$C1000=B10),--('Transaction Data'!$O1:$O10000)),0) will work, note that you cannot use the full range as in C:C but the above will count entries where C1:C1000 equals B10 and O1:O1000 0 -- Regards, Peo Sjoblom "Natalia" wrote in message ... =IF(D100,COUNTif('Transaction Data'!$C:$C,$B10,'Transaction Data'!O:O),"0"),0) This formula won't work. I am getting a message, "too many arguments in the function". I need this formula to work when the total in the first worksheets is 0, then to count # of transaction in the separate worksheet that are 0 (not all transactions). In addition, I need to count the # of transactions 0 only for a specific range determined by $B10 from the first worksheet. Can you help? Thank you. Natalia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel should have a function to count sheets | Excel Worksheet Functions | |||
How do I add the count function to the toolbar? | Excel Worksheet Functions | |||
Can I count in an array based on a function? | Excel Worksheet Functions | |||
PivotTable Macro Reverts to Count Function | Excel Worksheet Functions | |||
Sum and Count Function | Excel Worksheet Functions |