Sumif with Multiple Sheets and Multiple Criteria

[Edward S]

I picked up this Formula from the Excel newsgroup, I need to add
atleast 2 more criteria to the formula so in total 3 Criterias

For example:
If Phase"&{1,2,3}&"!B1:B146")=B4 and Phase"&{1,2,3}&"!C1:C146")=C4 and
Phase"&{1,2,3}&"!E2:BY2"),E3 then Sum the range within the multiple
sheets

=SUMPRODUCT(SUMIF(INDIRECT("Phase"&{1,2,3}&"!B1:B146"),B4,INDIRECT("Phase"&{1,2,3}&"!E4:BY146")))

Appreciate if someone could point in the right direction

Regards
Edward

 

[Frank Kabel]

Hi
Two possible approaches:

1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). Note: I haven't tried this in much detail -> so no
guarantee it will work. e.g. you may use the following formula

=SUM((THREED('sheet2:sheet50'!A1:A1000)="Book")*(THREED('sheet2:sheet50
'!B1:B1000="Yellow")*(THREED('sheet2:sheet50'!C1:C1000))))
enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column C in which column A and column B contain your
criteria

 

[Edward S]

Hi
Two possible approaches:

1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). Note: I haven't tried this in much detail -> so no
guarantee it will work. e.g. you may use the following formula

=SUM((THREED('sheet2:sheet50'!A1:A1000)="Book")*(THREED('sheet2:sheet50
'!B1:B1000="Yellow")*(THREED('sheet2:sheet50'!C1:C1000))))
enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column C in which column A and column B contain your
criteria

Hi Frank, I have tried both the approaches and both the approaches
have limitation with respect to the criteria;
1. The criterias have to be a text field i.e. in my situation if
(B1:B146=B4 then... ) has be a text field. I have tried Halan Grove's
example on his site ftp://members.aol.com/hrlngrv/scms.zip Excel
file: summing&countingmultiplesheets.xls

How do I change the Formula to evaluate a Number criteria

2. Both approaches only deal with criteria if they are Vertical and
NOT horizontal
One of my criteria is horizontal example (if E2:BY2"= E3 then... )

How do I get the criteria to work in the horizontal manner

I would appreciate if you could put the formula for me together or
provide some more hints, would be very helpful. I could send you my
file if you told me where I could post the same

Many thanks in advance for all your help

Regards
Edward

 

[Frank Kabel]

Hi
email me your file
email: frank[dot]kabel[at]freenet.[dot]de

But You may also consider changing your spreadsheet layout as Excel is
not very good with 3D references (as you can see looking at these
complicated solutions). Please describe in your email exactly what
you're trying to compare and your expected results

 

[Peo Sjoblom]

Hi Frank, I have tried both the approaches and both the approaches

have limitation with respect to the criteria;
1. The criterias have to be a text field i.e. in my situation if
(B1:B146=B4 then... ) has be a text field. I have tried Halan Grove's
example on his site ftp://members.aol.com/hrlngrv/scms.zip Excel
file: summing&countingmultiplesheets.xls

How do I change the Formula to evaluate a Number criteria

2. Both approaches only deal with criteria if they are Vertical and
NOT horizontal
One of my criteria is horizontal example (if E2:BY2"= E3 then... )

How do I get the criteria to work in the horizontal manner

I would appreciate if you could put the formula for me together or
provide some more hints, would be very helpful. I could send you my
file if you told me where I could post the same

Many thanks in advance for all your help

Regards
Edward

There are some things that don't make sense, how can you compare the single
horizontal range with the 2 other ranges?
Where would the AND condition be TRUE?

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[hgrove]

Edward S wrote...

I picked up this Formula from the Excel newsgroup, I need to
add at least 2 more criteria to the formula so in total 3 Criterias

For example:
If Phase"&{1,2,3}&"!B1:B146")=B4
and Phase"&{1,2,3}&"!C1:C146")=C4
and Phase"&{1,2,3}&"!E2:BY2"),E3
then Sum the range within the multiple sheets

=SUMPRODUCT(SUMIF(INDIRECT("Phase"&{1,2,3}
&"!B1:B146"),B4,INDIRECT("Phase"&{1,2,3}&"!E4:BY146")))

...

There are a few problems with this sample formula, but the main one i
that the criteria range is B1:B146 ignoring worksheets while the resul
range is E4:BY146. That the former is a single column while the latte
spans 73 columns is not a big deal, but the former spans 146 rows whil
the latter spans only 143 rows. That can't be accomodated. Either rang
could span a single column or a single row, but when either span
multiple rows or columns, the other *must* either a single row o
column or the same number of rows or columns.

Anyway, the trick to doing this is that you need to convert you
criteria and values into 2D ranges, and INDIRECT and OFFSET are th
only functions that help with this, though both must be wrapped insid
either N() or T(). In your case, I believe you could use

=SUMPRODUCT(
(N(INDIRECT("'Phase"&(1+INT((ROW(INDIRECT("1:438"))-1)/146))
&"'!B"&(1+MOD(ROW(INDIRECT("1:438"))-1,146))))=B4)*
(N(INDIRECT("'Phase"&(1+INT((ROW(INDIRECT("1:438"))-1)/146))
&"'!C"&(1+MOD(ROW(INDIRECT("1:438"))-1,146))))=C4)*
(N(INDIRECT("'Phase"&(1+INT((ROW(INDIRECT("1:438"))-1)/146))
&"'!R2C"&COLUMN(INDIRECT("E:BY")),0))=E3),
N(INDIRECT("'Phase"&(1+INT((ROW(INDIRECT("1:438"))-1)/146))
&"'!R"&(1+MOD(ROW(INDIRECT("1:438"))-1,146))&"C"
&COLUMN(INDIRECT("E:BY")),0))
)

I did test this, but on a smaller 3-worksheet block of cells. I'
assuming all your criteria cells are numeric, so wrapping them insid
N(). If any are text, change N() to T() for those criteria