Sum to exclude blank row

[Elijah]

Hi, I have a problem with trying to implement a summation formula. I have
monthly data in columns (A to L for each month) and rows being the data.

What I'd like to do is sum each month but exclude the value which is in a
row of data that doesn't have a full 12 months of data. So basically I only
want to SUM those sets of data which have a full 12 months.

Is there a way to do this?

Any help greatly appreciated.

Elijah

 

[Max]

One approach:

Assume your table is in A1:L6,
headers in row1, data in row2 down

Put in M2: =COUNT(A2:L2)
Copy M2 down to M6

Col M will be used as the "criteria" col
in the array formula we're going to set-up
to sum each of the cols A to L below, the criteria being
to only sum those rows where col M resolves to 12,
which denotes a full 12 months' data

Put in A8 (paste into the formula bar for A8):
=SUM(IF($M$2:$M$6=12,A2:A6))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Done correctly Excel will wrap curly braces { }
around the formula, viz.:
{=SUM(IF($M$2:$M$6=12,A2:A6))}
(don't type-in the braces !)

Copy A8 across to L8

A8:L8 will return the results wanted

 

[Elijah]

Thanks Max - that would do it.

I guess I could also use the standard SUMIF function for this as well?

But would you know how to apply the SUMIF (array or not) using two criteria?
You see the whole data set is by region. So I want to sum that region (with
only 12 months).

cheers

Elijah

 

[Frank Kabel]

Hi
for multiple riteria use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Elijah]

I think i figured it out:

I've just embedded a multiplier with in the array you provided. thanks
I guess I could also use SUMPRODUCT to do this.

I wonder which is better though an array SUM(IF or SUMPRODUCT?

cheers

 

[Peo Sjoblom]

SUMPRODUCT is better

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Max]

You're welcome, Elijah !
Thanks for the feedback

The link given by Frank in the other branch of the thread, viz.:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
points to a page in Bob Phillip's website which provides an
excellent and comprehensive discussion on "Multiple Condition Tests"

Looks like SUMPRODUCT has an edge over SUM(IF ..)
- see the section on "Advantages of SUMPRODUCT"
It's also less unwieldy and need not be array-entered.

 

[hgrove]

Elijah wrote...
...

I've just embedded a multiplier with in the array you provided.
thanks I guess I could also use SUMPRODUCT to do this.

I wonder which is better though an array SUM(IF or
SUMPRODUCT?

Neither may be better depending on how your data is laid out. If yo
have region identifiers in col A (A2:A17), year in col B (B2:B17), an
monthly data in columns C through N (C2:N17), and you want to sum al
entire years corresponding to region X, then you could use a singl
formula for your conditional sums.

=SUMPRODUCT((A2:A17=$A$21)*(MMULT(--ISNUMBER(C2:N17),{1;1;1;1;1;1;1;1;1;1;1;1})=12)*{1,1,1,1,1,1,1,1,1,1,1,1},
C2:N17