Dear All

I have a worksheet where all the values produced by list and all the cells
have either yes or no.
In the columns i have scenario by person, so imagine in columns C,E,G, I, K
i have the scanrio Budget and in columns D, F,H,J,L i have the scenario
Actual.

Who can i count all the yes for scenario budget (C,E,G, I, K) and all the
yes for the scenario actual (D, F,H,J,L) ?

I tried the count if but it doesn't accept seperatly cells. I need a dynamic
way.

Thanks in advance for your help

=SUMPRODUCT((MOD(COLUMN(C2:L20),2)=1)*(C2:L20="yes "))

--
__________________________________
HTH

Bob

"Manos" wrote in message
...
Dear All

I have a worksheet where all the values produced by list and all the cells
have either yes or no.
In the columns i have scenario by person, so imagine in columns C,E,G, I,
K
i have the scanrio Budget and in columns D, F,H,J,L i have the scenario
Actual.

Who can i count all the yes for scenario budget (C,E,G, I, K) and all the
yes for the scenario actual (D, F,H,J,L) ?

I tried the count if but it doesn't accept seperatly cells. I need a
dynamic
way.

Thanks in advance for your help

Dear Bob, thank you for you help, but the results is not correct.
Imagine a line where i want to add column by column (budget) and the rest of
the columns (actual) seperatly. All the columns have the same values, yes or
no, depending of the scenario.


"Bob Phillips" wrote:

=SUMPRODUCT((MOD(COLUMN(C2:L20),2)=1)*(C2:L20="yes "))

--
__________________________________
HTH

Bob

"Manos" wrote in message
...
Dear All

I have a worksheet where all the values produced by list and all the cells
have either yes or no.
In the columns i have scenario by person, so imagine in columns C,E,G, I,
K
i have the scanrio Budget and in columns D, F,H,J,L i have the scenario
Actual.

Who can i count all the yes for scenario budget (C,E,G, I, K) and all the
yes for the scenario actual (D, F,H,J,L) ?

I tried the count if but it doesn't accept seperatly cells. I need a
dynamic
way.

Thanks in advance for your help

Bob's suggestion relies on the data (Yes) being in an odd numbered column. If
you insert a single column, you may not get the results you want.

If I had to worry about counting yes's for certain columns (and I could
insert/delete columns whenever I wanted), then I'd use a dedicated row (row 1?)
and put an indicator in those cells. (I'd hide that row, too.)

=sumproduct(--($1:$1="Manos"),--(2:2="yes"))

This'll count the number of Yes's in row two that have Manos in row 1.

If you insert/delete a column, remember to fix row one (if you have to).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Manos wrote:

Dear Bob, thank you for you help, but the results is not correct.
Imagine a line where i want to add column by column (budget) and the rest of
the columns (actual) seperatly. All the columns have the same values, yes or
no, depending of the scenario.

"Bob Phillips" wrote:

=SUMPRODUCT((MOD(COLUMN(C2:L20),2)=1)*(C2:L20="yes "))

--
__________________________________
HTH

Bob

"Manos" wrote in message
...
Dear All

I have a worksheet where all the values produced by list and all the cells
have either yes or no.
In the columns i have scenario by person, so imagine in columns C,E,G, I,
K
i have the scanrio Budget and in columns D, F,H,J,L i have the scenario
Actual.

Who can i count all the yes for scenario budget (C,E,G, I, K) and all the
yes for the scenario actual (D, F,H,J,L) ?

I tried the count if but it doesn't accept seperatly cells. I need a
dynamic
way.

Thanks in advance for your help





--

Dave Peterson