Excel Formula Count non-Adjacent cells 3

[Thomas5000]

I have a list of cells and i want to count if a Y for yes was placed in the cell. But since it is a table of items, i only want that row to be counted. However countif only takes a range. I can't say countif(a1,a5, a11, a16, "y"). Is there a good alternative.I have a lot of cells.

Thanks!

 

[firefytr]

Hi there,

Is there a rhyme or reason to which cells?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

Hi,

So, are you saying in your example that although you want to count values of "y" in a1,a5, a11, a16, there may ALSO be "y" values in a2:a4, a6:A10, a12:a15 that you DON'T want to count?

Skip,

_[red][/red]

 

[Thomas5000]

Thanks guys

As i was saying this is a table. It is quite like a form that the Lab Director was already using.

On each sheet are two tables for each medial records cart or patient.

The lab director has to find out when errors take place. So he has a chart with columns such as
Where did error take place
On Encounter form, Progress Note, Results (these are columns)

Items being audited might be HCL, UDQ, P84 within the rows.

These rows contain the items they are checking for.

Since there are several of these tables in a spread sheet as many as 50, i would like to count the percentage of errors at the end according to the items listed.

So if there was a HCL error on the encounter form then a Y will be placed in the cell under Encounter and on the row for HCL. If an HCL error took place on the Progress Note then it would be checked and so on. If there is no error than N is placed into the box. A percentage of times an error took place will be calculated.

A range would require that i group the cells together somehow or do a percentage across or down. I'm only wanting data related to the intersected column and rows. For example the percentage of time there was an error for P84 on the Encounter, Progress Note, Lab Results

I could create an Access DB but that would be a lot more work to do and would require more on the user end to figure out how to work it. I'm just trying to do something fast, at least for now.

Thanks. I hope you can help me.

 

[ThomasLafferty]

Hi there! Can you post a link to your spreadsheet so we can take a look at it? Someone may even be able to suggest a better method than what you are thinking, but I for one could use a visual frame of reference.

Tom

Born once die twice; born twice die once.

 

[SkipVought]

So you might have 2 or more different columns to count. So count each separately. COUNTIF will work.

Skip,

_[red][/red]

 

[Thomas5000]

So, are you saying in your example that although you want to count values of "y" in a1,a5, a11, a16, there may ALSO be "y" values in a2:a4, a6:A10, a12:a15 that you DON'T want to count? Skip

Yes skip, that seems correct. I only want to count the values according to (or based on) category rather than ranges.

 

[Thomas5000]

I don't see how i can upload the file or make a visual representation on this forum. Some forums allow you to create tables within the messages. This one doesn't. I'll see if i can come up with a different idea.

Thanks.

 

[SkipVought]

"... I only want to count the values according to (or based on) category rather than ranges...."

Example:[tt]
A B
1 1
2
3
4 Y
5 Y 1
6
7 Y
8
9
10
11 Y 1
[/tt]
So according to your criteria, the count you are looking for in the above example, would be 2, because ONLY rows 1,5 & 11 are to be counted if the contain a Y.

If that's the case, you need another column like I have indicated
[tt]
=SUMPRODUCT((A1:A11="Y")*(B1:B11))
[/tt]




Skip,

_[red][/red]

 

[Thomas5000]

Ok the current table can be found here. shortened it.

http://www.wingfling.org/wingfling/Position-Absolute/excel.htm

The colors correspond with the way they should be counted.
The bottom table contains the total number of Y's in the cells. Actually i want the percentage that are Y vs N but it is simpler to put a number instead for demonstration purposes.

So the number of times Y appears in B3, B9, B15 is displayed in B21. I messed up on the Blues (it should be 3)but i think the other colors work.

Please help. This should be simple.

 

[ThomasLafferty]

OK! You will want to use a countif in an array formula. Here's how:

1. Copy and paste this formula into cell B21

    =SUM(IF($A$3:$A$18=$A21,IF(B$3:B$18="Y",1,0),0))

2. Hold down Ctrl + Shift then press enter. This makes the formula an array. If you notice in your formula bar, the formula is surrounded by braces { }

[!]{[/!]=SUM(IF($A$3:$A$18=$A21,IF(B$3:B$18="Y",1,0),0))[!]}[/!]

3. CRITICAL NOTE: If you alter the formula, you [!] must [/!]press control shift enter to reenter it as an array.

4. Autofill the formula down through row 24 then to the right across the D column.

Voila!

Hope this helps!

Tom

Born once die twice; born twice die once.

 

[SkipVought]

Is this what you mean...
[tt]
=sumproduct(--(A3:A999="HTC")*(B3:B999="Y"))
[/tt]


Skip,

_[red][/red]

 

[Thomas5000]

Hey, Skip, I kind of understand the logic behind Thomas' post. It will probably make more sense once i try it, but I understand the logic behind the formula you provided less.

What i the "--" for?

In the meantime I guess i'll try both look up sumproduct.

Thanks guys!

 

[firefytr]

The "--" is a double minus, which acts as a coercion for the test. You dno't actually need it with Skip's function and you can delete them. The reason for this is there are multiple conditions in a single array (check SUMPRODUCTS function in the Help files to see how it uses arrays in it's syntax), which, when checked against each other, actually coerce each other in lieu of the mathematical factor (*).

Basically, you need some type of mathematical conversion for it, because it will return a boolean (True/False). It's best to see if you step through your formula auditor. There are multiple ways to write Skip's formula..

=SUMPRODUCT(--(A3:A999="HTC")*(B3:B999="Y"))
=SUMPRODUCT((A3:A999="HTC")*(B3:B999="Y"))
=SUMPRODUCT(0+(A3:A999="HTC"),0+(B3:B999="Y"))
=SUMPRODUCT(1*(A3:A999="HTC"),1*(B3:B999="Y"))
=SUMPRODUCT(1*(A3:A999="HTC"),0+(B3:B999="Y"))
=SUMPRODUCT(0+(A3:A999="HTC"),--(B3:B999="Y"))
=SUMPRODUCT(1*(A3:A999="HTC"),--(B3:B999="Y"))

They should all yield the same results. The different (most typical) ways of coercion for these are as follows ..

--
0+
1*

All will do the same thing. Many argue one way is better than the other, but they're all really the same. It depends on your style, what you prefer and what's easiest for you to understand. Most people use the double unary minus' because it's faster to type generally, plus it's pretty much nowadays the de facto standard, so many people use it.

The coercion comes into play when we need to get Excel's boolean structure into numerical form. When thinking about this, think of True = 1, False = 0. So if your array returns something like this ..

{TRUE,TRUE,FALSE,FALSE,TRUE}

Then upon coercion, it will look like this ..

{1,1,0,0,1}

You can use this as a conditional count or sum. When pitching multiple arrays against each other (to get your multi-conditional portion), we already know that anything multiplied by 0 is 0. The arrays are matched up against each other (hence they need to be the same size, else an error will occur). So let's look at two arrays like the above example..

{TRUE,TRUE,FALSE,FALSE,TRUE}*{FALSE,FALSE,FALSE,FALSE,TRUE}

.. upon coercion ..

{1,1,0,0,1}*{0,0,0,0,1}

Lines these two up think of them like so..

{1,1,0,0,1}
{0,0,0,0,1}

.. they are multiplied against each other, which would return..

{1*0,1*0,0*0,0*0,1*1}

.. hence returning..

{0,0,0,0,1}

So in this case, only the last condition satisfied the conditional check. Adding another array will only compute the last value in that check, or, if only counting, will only count the last value.

There is a large difference when using + instead of * signs. The * sign means a logical AND operator, while the + sign means a logical OR operator. This is because we're not multiplying each array, we're adding them.

So these two arrays (above) would look like this ..

{1,1,0,0,1}+{0,0,0,0,1}

Lines these two up think of them like so..

{1,1,0,0,1}
{0,0,0,0,1}

.. hence returning..

{1,1,0,0,2}

Now, this does not mean that it will return the last value twice, it just means that it now counts (or sums) the first, second and fifth value of the array; basically anything greater than zero.

As I can't explain it all very well, here are some excellent resources on the SUMPRODUCT function..

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

(Bob Phillips)

http://www.mrexcel.com/wwwboard/messages/8961.html

(Aladin Akyurek)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Thomas5000]

thanks for the info. I really think this is helpful. Thanks very much

 

[Thomas5000]

it did work thanks!