Sum within table with column and row conditions 1

[amrog]

I have a sheet with the following data:

A B C D E F G H M
1 Code Jan Feb Mar Apr May Jun Jul ------- Dec
2 A 10 11 1 5 9 3 10 8
3 B 4 2 33 9 1 0 12 4
4 C
5 D
6 E
7 F 13 9 0 5 8 11 9 5

I need to sum the whole table B2:M7 with the condition that code (A:A) = "B" and month 1:1 "<"& today()

I am trying to use:
SUMIFS(B2:M7;A:A;"=B";1:1;"<"&today())

the months values in 1:1 are in 1/1/2008, 1/2/2008 until 1/12/2008.

 

[amrog]

I forgot to say that the sumifs function i used is not working and gives value#.

I need assistance for other solutions any expert of you has used.

 

[SkipVought]

Hi,

In Row 1 you have Months that look like TEXT.

If row 1 is not REAL DATES that is FORMATTED to display Month, then your SUMIF function will not work.

Bottom line: You need REAL DATES in row 1, like
[tt]
B1: enter 1/1/2008
C1: =DATE(YEAR(B1),MONTH(B1)+1,10
Copy C1 across.
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[amrog]

Actually row 1 contains real dates that represent the first date of each month.

 

[SkipVought]

You havee too many criteria for SUMIF. Check out SUMPRODUCT. I am using named ranges: Code is your range of codes, DateRange is the Date Range and DataRange is just the area containing the values you are SUMMING...
[tt]
=SUMPRODUCT((Code=A10)*(DateRange<TODAY())*(DataArea))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I am sorry that I missed your clear statement, "the months values in 1:1 are in 1/1/2008, 1/2/2008 until 1/12/2008."


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[amrog]

Many thanks for your support. Your solution worked fine for me.

I got another solution, but seems to be more complicated than your simple one:

{=SUM(MMULT(DataArea*--(Code=A10);--(TRANSPOSE(DateRange)<TODAY())))}

This is based on Matrix multiplication.

What do you think, which one will be less using CPU resources when applied on large of rows with variable codes?

For the moment I will use yours for simplicity.