Remember Me?

 markx Posts: n/a SUM/COUNT column(s) based on specific value present within the column

Hello everybody,

I'm wondering if it could be possible to "tell" Excel (through some
formulas, not VBA) to:
First: find the columns where the value present in row 3 is equal to
a specific variable that we indicate, and then
Second: make some operation based on the values in this (or these)
column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...)

Once again, apparently I'm too limited to work it out by myself :-).
If you were confronted with such a problem or know how to resolve it, I
would be grateful for any hints!

Mark

 Bob Phillips Posts: n/a First. Conditional formatting. Look up 'Highlight cells that meet specific
conditions' in the Answer wizard in help.

Second. Is this just where row 3 equals to value you sum everything in those
columns?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
Hello everybody,

I'm wondering if it could be possible to "tell" Excel (through some
formulas, not VBA) to:
First: find the columns where the value present in row 3 is equal

to
a specific variable that we indicate, and then
Second: make some operation based on the values in this (or these)
column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them

etc...)

Once again, apparently I'm too limited to work it out by myself :-).
If you were confronted with such a problem or know how to resolve it, I
would be grateful for any hints!

Mark

 markx Posts: n/a My fault: I wasn't clear enough!

I would like to mix both steps, not just resolve one after another. More
concretly:

Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f. ex.
"abc"
Then, there are some calculations that are done dynamically on "Sheet 2"
(there are like 200 columns and 300 rows where the data can change). All we
know is that in the row 3 we will have the titles of the column listed
(that's the data we are looking for!). In our special case we are looking
for the column called "abc".

So, for the time being, and once all the initial calculations are done, I go
to the "sheet 2", find the column where the value in row 3 are equal to
"abc" and then make several calculations on the numbers that are below the
row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE etc...).

Then, on the "Sheet 1", I take the rest of values present in the first
column ("A2", "A3", "A4" etc...), and repeat the whole procedure.

For my special case we can suppose that there will be always ONLY ONE column
named like the values we take from "Sheet 1", however it would be also great
to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the
values present in all the columns that has "abc" in row 3 (or whatever else
we need)).

I thought to use INDIRECT formula, but don't now exactly how to go with it
in this particular case.
Thanks once again,

Mark

"Bob Phillips" wrote in message
...
First. Conditional formatting. Look up 'Highlight cells that meet specific
conditions' in the Answer wizard in help.

Second. Is this just where row 3 equals to value you sum everything in

those
columns?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
Hello everybody,

I'm wondering if it could be possible to "tell" Excel (through some
formulas, not VBA) to:
First: find the columns where the value present in row 3 is equal

to
a specific variable that we indicate, and then
Second: make some operation based on the values in this (or these)
column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them

etc...)

Once again, apparently I'm too limited to work it out by myself :-).
If you were confronted with such a problem or know how to resolve it, I
would be grateful for any hints!

Mark

 Bob Phillips Posts: n/a Hi Mark,

I think this is what you want

=SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25))
=AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25)))
=COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25)))

all of these are array formulas, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
My fault: I wasn't clear enough!

I would like to mix both steps, not just resolve one after another. More
concretly:

Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f. ex.
"abc"
Then, there are some calculations that are done dynamically on "Sheet 2"
(there are like 200 columns and 300 rows where the data can change). All

we
know is that in the row 3 we will have the titles of the column listed
(that's the data we are looking for!). In our special case we are looking
for the column called "abc".

So, for the time being, and once all the initial calculations are done, I

go
to the "sheet 2", find the column where the value in row 3 are equal to
"abc" and then make several calculations on the numbers that are below the
row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE

etc...).

Then, on the "Sheet 1", I take the rest of values present in the first
column ("A2", "A3", "A4" etc...), and repeat the whole procedure.

For my special case we can suppose that there will be always ONLY ONE

column
named like the values we take from "Sheet 1", however it would be also

great
to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the
values present in all the columns that has "abc" in row 3 (or whatever

else
we need)).

I thought to use INDIRECT formula, but don't now exactly how to go with it
in this particular case.
Thanks once again,

Mark

"Bob Phillips" wrote in message
...
First. Conditional formatting. Look up 'Highlight cells that meet

specific
conditions' in the Answer wizard in help.

Second. Is this just where row 3 equals to value you sum everything in

those
columns?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
Hello everybody,

I'm wondering if it could be possible to "tell" Excel (through some
formulas, not VBA) to:
First: find the columns where the value present in row 3 is

equal
to
a specific variable that we indicate, and then
Second: make some operation based on the values in this (or these)
column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them

etc...)

Once again, apparently I'm too limited to work it out by myself :-).
If you were confronted with such a problem or know how to resolve it,

I
would be grateful for any hints!

Mark

 markx Posts: n/a You are a wizard Bob!
So, once again, the ARRAY thing was the solution!!

Many thanks,
Mark

"Bob Phillips" wrote in message
...
Hi Mark,

I think this is what you want

=SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25))
=AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25)))
=COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25)))

all of these are array formulas, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
My fault: I wasn't clear enough!

I would like to mix both steps, not just resolve one after another. More
concretly:

Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f.

ex.
"abc"
Then, there are some calculations that are done dynamically on "Sheet 2"
(there are like 200 columns and 300 rows where the data can change). All

we
know is that in the row 3 we will have the titles of the column listed
(that's the data we are looking for!). In our special case we are

looking
for the column called "abc".

So, for the time being, and once all the initial calculations are done,

I
go
to the "sheet 2", find the column where the value in row 3 are equal to
"abc" and then make several calculations on the numbers that are below

the
row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE

etc...).

Then, on the "Sheet 1", I take the rest of values present in the first
column ("A2", "A3", "A4" etc...), and repeat the whole procedure.

For my special case we can suppose that there will be always ONLY ONE

column
named like the values we take from "Sheet 1", however it would be also

great
to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the
values present in all the columns that has "abc" in row 3 (or whatever

else
we need)).

I thought to use INDIRECT formula, but don't now exactly how to go with

it
in this particular case.
Thanks once again,

Mark

"Bob Phillips" wrote in message
...
First. Conditional formatting. Look up 'Highlight cells that meet

specific
conditions' in the Answer wizard in help.

Second. Is this just where row 3 equals to value you sum everything in

those
columns?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
Hello everybody,

I'm wondering if it could be possible to "tell" Excel (through some
formulas, not VBA) to:
First: find the columns where the value present in row 3 is

equal
to
a specific variable that we indicate, and then
Second: make some operation based on the values in this (or

these)
column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them
etc...)

Once again, apparently I'm too limited to work it out by myself :-).
If you were confronted with such a problem or know how to resolve

it,
I
would be grateful for any hints!

Mark

 markx Posts: n/a Getting back to you Bob once again...

Do you know how to adapt the formulas if we want to avoid #DIV/0! thing? I
get this message for all the AVERAGES that the formula is unable to
calculate (there is no corresponding value in our row 3...). I thought I can
add a supplementary ,"") somewhere at the end (to terminate IF
expression), but it's not functionning:-(

Many thanks,
Mark

"Bob Phillips" wrote in message
...
Hi Mark,

I think this is what you want

=SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25))
=AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25)))
=COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25)))

all of these are array formulas, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
My fault: I wasn't clear enough!

I would like to mix both steps, not just resolve one after another. More
concretly:

Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f.

ex.
"abc"
Then, there are some calculations that are done dynamically on "Sheet 2"
(there are like 200 columns and 300 rows where the data can change). All

we
know is that in the row 3 we will have the titles of the column listed
(that's the data we are looking for!). In our special case we are

looking
for the column called "abc".

So, for the time being, and once all the initial calculations are done,

I
go
to the "sheet 2", find the column where the value in row 3 are equal to
"abc" and then make several calculations on the numbers that are below

the
row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE

etc...).

Then, on the "Sheet 1", I take the rest of values present in the first
column ("A2", "A3", "A4" etc...), and repeat the whole procedure.

For my special case we can suppose that there will be always ONLY ONE

column
named like the values we take from "Sheet 1", however it would be also

great
to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the
values present in all the columns that has "abc" in row 3 (or whatever

else
we need)).

I thought to use INDIRECT formula, but don't now exactly how to go with

it
in this particular case.
Thanks once again,

Mark

"Bob Phillips" wrote in message
...
First. Conditional formatting. Look up 'Highlight cells that meet

specific
conditions' in the Answer wizard in help.

Second. Is this just where row 3 equals to value you sum everything in

those
columns?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
Hello everybody,

I'm wondering if it could be possible to "tell" Excel (through some
formulas, not VBA) to:
First: find the columns where the value present in row 3 is

equal
to
a specific variable that we indicate, and then
Second: make some operation based on the values in this (or

these)
column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them
etc...)

Once again, apparently I'm too limited to work it out by myself :-).
If you were confronted with such a problem or know how to resolve

it,
I
would be grateful for any hints!

Mark

 Bob Phillips Posts: n/a Mark,

I assume it is just teh AVERAGE that you are having problems with.
Therefore, try this

=AVERAGE(IF(COUNTIF(Sheet2!B3:M3,A1)0,IF(Sheet2!B 3:M25<"",IF(Sheet2!B3:M3=
A1,Sheet2!B3:M25))))

again array entered

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
Getting back to you Bob once again...

Do you know how to adapt the formulas if we want to avoid #DIV/0! thing? I
get this message for all the AVERAGES that the formula is unable to
calculate (there is no corresponding value in our row 3...). I thought I

can
add a supplementary ,"") somewhere at the end (to terminate IF
expression), but it's not functionning:-(

Many thanks,
Mark

"Bob Phillips" wrote in message
...
Hi Mark,

I think this is what you want

=SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25))
=AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25)))
=COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25)))

all of these are array formulas, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
My fault: I wasn't clear enough!

I would like to mix both steps, not just resolve one after another.

More
concretly:

Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f.

ex.
"abc"
Then, there are some calculations that are done dynamically on "Sheet

2"
(there are like 200 columns and 300 rows where the data can change).

All
we
know is that in the row 3 we will have the titles of the column listed
(that's the data we are looking for!). In our special case we are

looking
for the column called "abc".

So, for the time being, and once all the initial calculations are

done,
I
go
to the "sheet 2", find the column where the value in row 3 are equal

to
"abc" and then make several calculations on the numbers that are below

the
row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE

etc...).

Then, on the "Sheet 1", I take the rest of values present in the first
column ("A2", "A3", "A4" etc...), and repeat the whole procedure.

For my special case we can suppose that there will be always ONLY ONE

column
named like the values we take from "Sheet 1", however it would be also

great
to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the
values present in all the columns that has "abc" in row 3 (or whatever

else
we need)).

I thought to use INDIRECT formula, but don't now exactly how to go

with
it
in this particular case.
Thanks once again,

Mark

"Bob Phillips" wrote in message
...
First. Conditional formatting. Look up 'Highlight cells that meet

specific
conditions' in the Answer wizard in help.

Second. Is this just where row 3 equals to value you sum everything

in
those
columns?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"markx" wrote in message
...
Hello everybody,

I'm wondering if it could be possible to "tell" Excel (through

some
formulas, not VBA) to:
First: find the columns where the value present in row 3 is

equal
to
a specific variable that we indicate, and then
Second: make some operation based on the values in this (or

these)
column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of

them
etc...)

Once again, apparently I'm too limited to work it out by myself

:-).
If you were confronted with such a problem or know how to resolve

it,
I
would be grateful for any hints!

Mark

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post dkhedkar Excel Worksheet Functions 1 March 5th 05 12:20 AM Troi-Xanh Excel Worksheet Functions 2 February 12th 05 12:46 AM Mctabish Excel Worksheet Functions 1 January 30th 05 10:40 AM Tamara Excel Worksheet Functions 1 January 19th 05 04:12 PM jmatchus Excel Worksheet Functions 0 January 17th 05 06:49 PM

All times are GMT +1. The time now is 02:25 PM. Copyright ©2004-2019 ExcelBanter.