SUMIF and COUNTIF over multiple sheets.

[Henrik]

Hello,


Here's a little challenge for all excel freaks out there. I am trying to make a conditional sum over multiple sheets. Each sheet lists the costs for a different work package of my project.


A simple sum works perfect:

=SUM(Sheet2:Sheet3!G:G) ' Sums correctly all costs listed in column G of Sheet2 and Sheet3


Now I'd like to do the same with SUMIF() and COUNTIF(), for example to make one sum for all costs in EUR and another for all costs in USD. However, here I run into a problem. The analogous COUNTIF formula returns #VALUE!

=COUNTIF(Sheet2:Sheet3!H:H;$A4) = #VALUE!


Same thing with the analogous SUMIF formula:

=SUMIF(Sheet2:Sheet3!H:H;$A4;Sheet2:Sheet3!G:G) = #VALUE!


Have also experimented a little with the SUMPRODUCT formula, just to see if it would work:

=SUMPRODUCT(Sheet2:Sheet3!C:C;Sheet2:Sheet3!E:E) = #REF!


Anybody got an idea how to solve the problem or an explanation to at least help me understand why it does not work?


Thanks a lot for your help!


/ Henrik

 

[Luke M]

You've encounted the limitation of 3d formulas, of which only a few are supported by XL. Mostly, they are the basic math functions (Sum, Average, Count, etc).


To accomplish your task, you need to break it down into multiple formulas. on each sheet, have a formula like:

=COUNTIF(H:H,MainSheet!$A4)

Copy down is needed.


Then, on your main sheet, you can use the 3d array to gather them all:

=SUM(Sheet2:Sheet3!CellWithFormula)


Link for further detail on 3d formulas:

http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx#BMlearn_more_about_a_3-d_refererence

 

[Henrik]

Thanks Luke!


Glad to hear an explanation to the problem although I am afraid that I can't solve it in such an elegant way as I had imagined this time.


Let's just hope that Microsoft will generalize the formulas in future versions of Excel for more 3D compatibility.