SUMIF + INDIRECT?

[Guest]

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?

 

[Sandy Mann]

There may be better ways but try something like:

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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bernard Liengme]

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

 

[Guest]

Thanks Sandy. I did get it with:

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk