SUMIF + INDIRECT?
 

I've used SUMIF for simple calculations such as:

SUMIF($F$32:$F$39,"Contracted",$H$32:$H$39)

on a single sheet.

How would I work that using INDIRECT to perform that same function for
multiple sheets?

I can't seem to make it work using a similar format to:

SUM(INDIRECT($B6&"!$H$17:$H$20") where B6 is the worksheet name I want to
reference.

In my example I am trying to sum the dollar amounts in column H for those
services that are labeled <Contracted in column F across multiple worksheets
(ie. for each worksheet, I want one total dollar amount).

Is it SUMIF plus INDIRECT or is there another way to go about it?

SUMIF + INDIRECT?
 

There may be better ways but try something like:

=SUMPRODUCT((INDIRECT(B6&"!$F$32:$F$39")="Contract ed")*INDIRECT(B6&"!$H$32:$H$39"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Davoud" wrote in message
...
I've used SUMIF for simple calculations such as:

SUMIF($F$32:$F$39,"Contracted",$H$32:$H$39)

on a single sheet.

How would I work that using INDIRECT to perform that same function for
multiple sheets?

I can't seem to make it work using a similar format to:

SUM(INDIRECT($B6&"!$H$17:$H$20") where B6 is the worksheet name I want to
reference.

In my example I am trying to sum the dollar amounts in column H for those
services that are labeled <Contracted in column F across multiple
worksheets
(ie. for each worksheet, I want one total dollar amount).

Is it SUMIF plus INDIRECT or is there another way to go about it?

SUMIF + INDIRECT?
 

This worked for me
=SUMIF(INDIRECT(B6&"!B1:B10"),"X",INDIRECT(B6&"!A1 :A10"))
B6 had the text Sheet2
If column B has an X then sum corresponding A cell on Sheet2
You know that sheet names with spaces in them need to be within single
quotes?

best wishes


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Davoud" wrote in message
...
I've used SUMIF for simple calculations such as:

SUMIF($F$32:$F$39,"Contracted",$H$32:$H$39)

on a single sheet.

How would I work that using INDIRECT to perform that same function for
multiple sheets?

I can't seem to make it work using a similar format to:

SUM(INDIRECT($B6&"!$H$17:$H$20") where B6 is the worksheet name I want to
reference.

In my example I am trying to sum the dollar amounts in column H for those
services that are labeled <Contracted in column F across multiple
worksheets
(ie. for each worksheet, I want one total dollar amount).

Is it SUMIF plus INDIRECT or is there another way to go about it?

SUMIF + INDIRECT?
 

Thanks Sandy. I did get it with:

=SUMIF(INDIRECT("'"&B6&"'!$F$32:$F$47"),"Contracte d",INDIRECT("'"&B6&"'!$H$32:$H$47"))


"Sandy Mann" wrote:

There may be better ways but try something like:

=SUMPRODUCT((INDIRECT(B6&"!$F$32:$F$39")="Contract ed")*INDIRECT(B6&"!$H$32:$H$39"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Davoud" wrote in message
...
I've used SUMIF for simple calculations such as:

SUMIF($F$32:$F$39,"Contracted",$H$32:$H$39)

on a single sheet.

How would I work that using INDIRECT to perform that same function for
multiple sheets?

I can't seem to make it work using a similar format to:

SUM(INDIRECT($B6&"!$H$17:$H$20") where B6 is the worksheet name I want to
reference.

In my example I am trying to sum the dollar amounts in column H for those
services that are labeled <Contracted in column F across multiple
worksheets
(ie. for each worksheet, I want one total dollar amount).

Is it SUMIF plus INDIRECT or is there another way to go about it?

SUMIF + INDIRECT?
 

Glad you got there - I told you that there were better ways <g

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Davoud" wrote in message
...
Thanks Sandy. I did get it with:

=SUMIF(INDIRECT("'"&B6&"'!$F$32:$F$47"),"Contracte d",INDIRECT("'"&B6&"'!$H$32:$H$47"))


"Sandy Mann" wrote:

There may be better ways but try something like:

=SUMPRODUCT((INDIRECT(B6&"!$F$32:$F$39")="Contract ed")*INDIRECT(B6&"!$H$32:$H$39"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Davoud" wrote in message
...
I've used SUMIF for simple calculations such as:

SUMIF($F$32:$F$39,"Contracted",$H$32:$H$39)

on a single sheet.

How would I work that using INDIRECT to perform that same function for
multiple sheets?

I can't seem to make it work using a similar format to:

SUM(INDIRECT($B6&"!$H$17:$H$20") where B6 is the worksheet name I want
to
reference.

In my example I am trying to sum the dollar amounts in column H for
those
services that are labeled <Contracted in column F across multiple
worksheets
(ie. for each worksheet, I want one total dollar amount).

Is it SUMIF plus INDIRECT or is there another way to go about it?