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!

Thanks in advance!
Mark

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!

Thanks in advance!
Mark

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!

Thanks in advance!
Mark

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!

Thanks in advance!
Mark

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!

Thanks in advance!
Mark

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!

Thanks in advance!
Mark

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!

Thanks in advance!
Mark