If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|