Help with Count if please :)

[dusty]

=(SUM(COUNTIF(test1;{"i","ii","iii"})*{1,0,0}))/(ROWS(test1)*(COLUMNS(test1)))

This the formula I am using at the moment. It calculates a percentage of cells labeled i in the range test1.

Is there a way of modifying this so that it only counts if the cell is labbelled i and another cell is labelled x.

So if cell b3 has value x and cell c3 has value i count = 1

if cell b3 has value x and cell c3 has value ii count = 0

if cell b3 has value blank and cell c3 has value i count =0

if cell b3 has value none and cell c3 has value ii count = 0

this is what i hope to achieve hope someone can help

Dusty

 

[Brianwarnock]

My guess is that you are on 2007 as I have never seen that construct for Countif.

Pre 2007 I would have named the array in col b as test1 and that in col c as test2 and used Sumproduct

=Sumproduct((test1="x")*(test2="i"))

I believe that in 2007 Sumif has been enlarged with a view to making sumproduct redundent so maybe you need to look there , or do as i have suggested.

Brian

 

[dusty]

solved used sum product

 

[dusty]

Thanks Brian didnt see your post until after I posted mine.

 

[Brianwarnock]

Not surprised if you look at the times, guess I was a millisecond ahead, happy you solved it.

Brian

 

[dusty]

This is what I use now and it works perfectly.

=(SUMPRODUCT((test1="i")*(CA="x"))/(ROWS(test1)*(COLUMNS(test1)))

The next issue is related to the named ranges.

There are actualy 3 columns with x's marked in them. Each column represents a different clasification of project. Class A, B and C selecting class A hides every row that does not have an x in the respective column for A.

Range CA is the range for this column.CB would be the range for the class B. CC range for class C.

The user uses option buttons to select their class of projects.

Is there anyway to be able to change the range in the sum product formula above on the click of an option button?

I guess it would be code. I will look into performing sum product by code. But if anyone gets there sooner please let me know.

Cheers Dusty

 

[Brianwarnock]

=(SUMPRODUCT((test1="i")*(CA="x"))/

As the value in CA or CB etc cannot influence the number of i in test1 wouldn't a simple Countif have worked?
If(CA= "x",countif(test1,"i"),0))


Sumproduct is really for handling arrays and matching cells therein as per your first explanation using b3 and c3 which I assumed continued down the columns.

Brian

 

[dusty]

Yes a simple count if would have worked. Sometimes we have a habbit of making things more complex than they actualy are.

The other problem was that I wished to have in effect three different formulas in one cell and 3 option buttons to flick between the formulas.

But I figured out the code for that so all is good.

Cheers for your help Brian.

Dusty