Sumifs and Countifs
 

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?

Sumifs and Countifs
 

It would help if you posted your exact formula.

"Costas Limassol" wrote:

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?

Sumifs and Countifs
 

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)...



"Costas Limassol" wrote:

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?

Sumifs and Countifs
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Costas Limassol" wrote:

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?