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

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

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

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