SUM/COUNT column(s) based on specific value present within the column

[markx]

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

 

[Bob Phillips]

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]

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]

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,Sheet2!B3:M25)))
=COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<>"",Sheet2!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]

You are a wizard Bob!
So, once again, the ARRAY thing was the solution!!

Many thanks,
Mark

 

[markx]

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]

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!B3:M25<>"",IF(Sheet2!B3:M3=
A1,Sheet2!B3:M25))))

again array entered

--

HTH

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