count multiple values from contiguous range

[Amer]

Hi there,

I need to create a formula that applies on text cells to count the frequency
of two text values from a contiguous range.

I used this formula but didn't work:

=SUM(IF((F19:F21="Completed")+(F19:F21="Failed");1;0))

but I received This #VALUE! error.!!!

The values (words) I'm looking for are "Completed" and "Failed". I want the
total number for both of them as a sum.

 

[Pete_UK]

That is an array formula - you need to commit it using CTRL-SHIFT-
ENTER instead of the usual <Enter>.

Here's an alternative non-array formula:

=COUNTIF(F19:F21;"Completed")+COUNTIF(F19:F20;"Failed")

I've used semicolons to separate the terms, as you have done.

Hope this helps.

Pete

 

[Bob Phillips]

or

=SUM(COUNTIF(F19:F21,{"Completed","Failed"}))

---
HTH

Bob Phillips

That is an array formula - you need to commit it using CTRL-SHIFT-
ENTER instead of the usual <Enter>.

Here's an alternative non-array formula:

=COUNTIF(F19:F21;"Completed")+COUNTIF(F19:F20;"Failed")

I've used semicolons to separate the terms, as you have done.

Hope this helps.

Pete

 

[herochenna]

Visit www.ExcelGoodies.Com