using sumif for 2nd level arrays

  • Thread starter Thread starter andrez
  • Start date Start date
A

andrez

Hello everyone

Please help me to find a way to calculate for each 'id1' interval su
of each 'id2' intervals' for data1, data2 etc.

Length of id1 and id2 columns is variable.

Source data looks like this:
A B C D etc
1 id1 id2 data1 data2 etc
2 a 1 x x
3 a 1 x x
4 a 1 x x
5 a 2 x x
6 a 2 x x
7 a 3 x x
8 a 3 x x
9 a 3 x x
10 c 2 x x
11 c 2 x x
12 c 3 x x
13 c 3 x x
...etc ...etc.
999

without 'id1' it would be just sumif(b2:b999,1,c2:c999)
but now I need to separate this datas per 'id1' intervals

Thanks forwar
 
Hi,

In the sample formula you gave, did you mean =SUMIF(B2:B999,"a",D2:D999) so
you could find out how many rows in column B are "a"?

If you did then I think you need to look at the SUMPRODUCT function.

If you then want to find out how many rows in column B are "a" and how many
in C are "2", then the formula would be:
=SUMPRODUCT((B2:B999="a")*(C2:C999=2)*D2:D999)

For interest, here is the original formula restated for SUMPRODUCT;
=SUMPRODUCT((B2:B999="a")*D2:D999)

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
A couple of options...

1) Build a pivot table from the data by dragging id1 to the ROW field, id2
to the COLUMN field, and data1 to the DATA field. Repeat the procedure for
summarizing data2.

2) Apply a formula for multiconditional summing:

=SUMPRODUCT(--($A$2:$A$13=$G2),--($B$2:$B$13=H$1),$C$2:$C$13)

where A2:A13 houses id1 values, B2:B13 id2 values, C2:C13 data1 values, G2
an id1 value (condition), and H1 an id2 value (condition).

3) If the data area is huge (1000 or more rows) and it is sorted on id1 (as
your sample implies), one can apply for efficiency/speed a SumIf formula on
computed subranges...
 
Andy said:
*Hi,

In the sample formula you gave, did you mea
=SUMIF(B2:B999,"a",D2:D999) so
you could find out how many rows in column B are "a"?

.....
If you then want to find out how many rows in column B are "a" an
how many
in C are "2", then the formula would be:
=SUMPRODUCT((B2:B999="a")*(C2:C999=2)*D2:D999)

For interest, here is the original formula restated for SUMPRODUCT;
=SUMPRODUCT((B2:B999="a")*D2:D999)

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"


*
Click to expand...
[/QUOTE]

Thank you for your attention.

But no, I did not messed up with SUMIF i've used.
I need to know sum of x's from 'data1' and 'data2' columns per 'id1
per 'id2' interval

And I do not need to know number of rows, I need sum only
 
Aladin said:
*A couple of options...

1) Build a pivot table from the data by dragging id1 to the RO
field, id2
to the COLUMN field, and data1 to the DATA field. Repeat th
procedure for
summarizing data2.

2) Apply a formula for multiconditional summing:

=SUMPRODUCT(--($A$2:$A$13=$G2),--($B$2:$B$13=H$1),$C$2:$C$13)

where A2:A13 houses id1 values, B2:B13 id2 values, C2:C13 data
values, G2
an id1 value (condition), and H1 an id2 value (condition).

3) If the data area is huge (1000 or more rows) and it is sorted o
id1 (as
your sample implies), one can apply for efficiency/speed a SumI
formula on
computed subranges...
*
Click to expand...
[/QUOTE]

Thanks a lot aLADIN. 2nd option was a GREAT hint for me. I appreciat
it
 
Back
Top