Sumifs and Countifs

[Costas Limassol]

I am using the sumifs and countifs functions to summarise data in a database.
The left column of my summary lists the values of one parameter and the
columns on the right use either the sumifs or the countifs. Above my
summary, I created two filter combo boxes where the user can select which
parameter to set as criteria_range so as to create an automated 4D summary
table.
The problem I have is that although my ifs functions correctly read the
parameters of the criteria_range from the combo boxes, they read it as a
label and not as a named range i.e. it reads the parameter as ="named_range"
instead of =named_range. I tried to convert my label to range but I couldn't
find anything suitable. Does anybody have any ideas?

 

[Bob Umlas, Excel MVP]

It would help if you posted your exact formula.

 

[Sheeloo]

If you parameter is in Cell A1 then use
INDIRECT(A1) in place of A1 in your formula.

For example if B1:B10 is named SALARY

and A1 contains the text SALARY use
=SUM(INDIRECT(A1))

instead of
=SUM(A1) which will evaluate to SUM("SALARY") as you mentioned instead of
SUM(SALARY)...

 

[Shane Devenshire]

Hi,

Bob is correct!

Here is my guess use INDIRECT.

So if the parameter is in A1
=INDIRECT(A1)
will use the range name in A1 as a range. Of course you need to put
something around this to make it useful. For example

=SUMIF(INDIRECT(A1),">0")
is the same as
=SUMIF(Data,">0") assuming A1 contains the text "Data", but its more dynamic.