Sumif across rows and columns

[primagic]

I am trying to sum across rows and columns


Section Jan 13 Feb 13 Mar 14
A 10 10 10
A 10 10 10
B 5 5 5

What I am trying to do is sum everying up until today for each section

 

[SkipVought]

Hi,

1. Are your headings actual dates? If you select one of your headings and change the NumberFormat to GENERAL, then the value should change to a NUMBER.

2. When you state, "sum everything" what does that mean with respect to your example?

 

[randy700]

Do you mean something like this?

[pre]Section Jan13 Feb13 Mar14 Total
A 10 10 10 =SUM($A$2:A5) A5 result - 30
A 10 10 10 =SUM($A$2:B5) B5 result - 60
B 5 5 5 =SUM($A$2:C5) C5 result - 75[/pre]


Randy

 

[SkipVought]

[pre]
Section Jan13 Feb13 Mar14
A 10 10 10
A 10 10 10
B 5 5 5
[/pre]

[pre]
Section Section Total
A =SUMIF(Sheet1!$A$2:$A$4,A2,Sheet1!$B$2:$D$4)
B
[/pre]

That's assuming that there are no dates greater than today.

 

[Andrzejek]

sum everying up until today for each section "
So I assume the outcome would be:[tt]

A 60
B 15[/tt]

?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[madonnac]

To sum a 3-D array, we need to use an array formula

First, the easy part - The Section:

(A2:A4="A")

This will give us true or false for each row.

Then we work down the rows, and see if the date is correct:

IF(B1:D1<TODAY(),B2:D4)

We multiply each row by the true/false from the section, which will zero out each row that isn't the right section

=SUM((A2:A4="A")*(IF(B1:D1<TODAY(),B2:D4)))

and finish it by making it an array function, which is the part that makes it look at each row, by using CTRL+SHIFT+ENTER to enter the formula, rather than just ENTER

You know when you have successfully done this, as excel will put curly brackets {} around the formula

 

[primagic]

I managed to get it working, however it works when the column number of the second index statement is 1. If I choose 2, 3 or any other number I get VALUE error.

Is this something to do with the data in column 2?

 

[SkipVought]

Please post your formula.

 

[primagic]

=SUMPRODUCT(('[bmi_excel_layer_June_Final_v3.xlsm]Main - Partnerships'!D6:D5000="UKPB")*INDEX('[bmi_excel_layer_June_Final_v3.xlsm]Main - Partnerships'!JR6:KC5000,0,1):INDEX('[bmi_excel_layer_June_Final_v3.xlsm]Main - Partnerships'!JR6:KC5000,0,1))

This works

=SUMPRODUCT(('[bmi_excel_layer_June_Final_v3.xlsm]Main - Partnerships'!D6:D5000="UKPB")*INDEX('[bmi_excel_layer_June_Final_v3.xlsm]Main - Partnerships'!JR6:KC5000,0,1):INDEX('[bmi_excel_layer_June_Final_v3.xlsm]Main - Partnerships'!JR6:KC5000,0,2))

This doesn't work

 

[SkipVought]

What do your INDEX() functions return? I have never seen them used in a SOMPRODUCT() like this!

Similarly, I've never seen a ZREO as a row or column offset in the INDEX() function???

 

[madonnac]

Index: use of the 0:

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively.
or
If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively

There is nothing wrong with the references it returns - with a 1, you get JR6:JR5000, with a 2 you get JR6:JS5000. I would check the ranges to make sure there are no errors in the 2nd column - these errors will carry over to the SUMPRODUCT formula