Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 21st 05, 02:57 PM
markx
 
Posts: n/a
Default 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



  #2   Report Post  
Old March 21st 05, 03:11 PM
Bob Phillips
 
Posts: n/a
Default

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




  #3   Report Post  
Old March 21st 05, 04:23 PM
markx
 
Posts: n/a
Default

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






  #4   Report Post  
Old March 21st 05, 11:09 PM
Bob Phillips
 
Posts: n/a
Default

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








  #5   Report Post  
Old March 22nd 05, 08:57 AM
markx
 
Posts: n/a
Default

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












  #6   Report Post  
Old March 22nd 05, 09:35 AM
markx
 
Posts: n/a
Default

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










  #7   Report Post  
Old March 22nd 05, 10:23 AM
Bob Phillips
 
Posts: n/a
Default

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














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I automatically hide columns in a worksheet based on a cell value? dkhedkar Excel Worksheet Functions 1 March 5th 05 12:20 AM
count number of cells based on TWO conditions (2 different columns Troi-Xanh Excel Worksheet Functions 2 February 12th 05 12:46 AM
Tallying columns based on values of 2 different columns Mctabish Excel Worksheet Functions 1 January 30th 05 10:40 AM
can you prevent viewing of specific columns in a worksheet Tamara Excel Worksheet Functions 1 January 19th 05 04:12 PM
Hiding columns based on user/password jmatchus Excel Worksheet Functions 0 January 17th 05 06:49 PM


All times are GMT +1. The time now is 09:28 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017