Hi,
is there a easier=quicker way to set multiple criteria but with the same input value for SUM array?
example:
A1 = 1
A2 = ""
my first row array is:
{=SUM((Base!$B$4:$B$10000=$A$1)*Base!C4:C10000)}
my second row is:
{=SUM((Base!$B$4:$B$10000=$A$2)*(Base!$C$4:$C$10000=$A$1)*Base!D4:D10000)}
3rd row:
{=SUM((Base!$B$4:$B$10000=$A$2)*(Base!$C$4:$C$10000=$A$2)*(Base!$D$4:$D$10000{=$A$1)*Base!E4:E10000)}
4th row
{=SUM((Base!$B$4:$B$10000=$A$2)*(Base!$C$4:$C$10000=$A$2)*(Base!$D$4:$D$10000=$A$2)*(Base!$E$4:$E$10000=$A$1)*Base!F4:F10000)}
as you can see I m adding in each following row an extra criteria is added which is asking are the cells in the extra column blank
I tried to change it as
{=SUM((Base!$B$4:$D$10000=$A$2)*Base!E4:E10000)}
but that woudnt work...
is there any other way of making this please?
I will have to do 52 (due to weeks) criteria like that and I m quite sure there is a limit on the sum function like there is on sumproduct...
Please help
Thanks a lot
If you need more info please ask
otherwise you can take a look at the link of my previous post which adds a full story
<http://www.excelforum.com/excel-programming/737831-counting-function-with-criteria-for-specific-week-in-the-year.html>
Bookmarks