sumif a 3d reference

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I hope someone can help me with a workaround for a sumif formula that
has to work on a 3d range, I'm trying to total values that meet
specified criteria on multiple worksheets without adding up 12 sumif
formulas, for example

A B

1 fees =sumif(jan:dec!$C$1:$C$100,A1,jan:dec!$D$1:$D$100)
2 office
3 supplies
4 shipping

I know that this formula will return a #value error, but is there a
way to emulate this using something like
=sum(jan:dec!$C$1:$C$100,A1,jan:dec!$D$1:$D$100)....?

as you can see, i didn't get too far

thanks in advance
Pete
 
One way:

On an out of the way on the Jan:Dec sheets (say, Z1), put the formula

=SUMIF($C$1:$C$100, Sheet1!A1, $D$1:$D$100)

(where Sheet1 is the sheet for your sum).

Then in Sheet1, enter

=SUM(jan:dec!Z1)
 
With the free morefunc.xll add-in which provides THREED...

=SUMPRODUCT(--(THREED(jan:dec!$C$1:$C$100)=A1),THREED(jan:dec!$D$1:$D$100))

Without morefunc...

List your sheet names say in L1:L12 and use

=SUMPRODUCT(SUMIF(INDIRECT("'"&$L$1:$L$12&"'!C1:C100"),A1,INDIRECT("'"&$L$1:
$L$12&"'!D1:D100")))
 
Hello,

why don't u try conditional sum wizard it is available in
add in function

with regards

Durai
 
Back
Top