Calculation of Selection Group using Arrays (or VBA?)

W

wezred

I posted this up on VBA Express but have not had any reply from them.
was wondering if I could get some help here.

Please see attached file. I'm trying to use arrays to sum a group o
numbers based on 2 variables which is selected on a separate cell.

I realised that it only works with 1 variable. I'm trying to make i
work with about 5 variable. Please see attached file. You can see tha
it says #N/A if I tried to do this with 2 variables.

=SUM(IF(($G$3:$G$15=TRANSPOSE((IF($B$3:$B$7="Yes",$A$3:$A$7))))*($H$3:$H$15=TRANSPOSE((IF($E$3:$E$5="Yes",$D$3:$D$5)))),($I$3:$I$15)))

This formula doesn't work!!

Any solution is really appreciated. Please note that you're not suppos
to create any additional columns or cells. All formula must be in 1 cel
and must also work if each variable has 50 selections.

Thank you

+-------------------------------------------------------------------
|Filename: arra.zip
|Download: http://www.excelforum.com/attachment.php?postid=3628
+-------------------------------------------------------------------
 
B

Bob Phillips

=SUMPRODUCT(--(ISNUMBER(MATCH(1&G1:G13,A1:A5&B1:B5,0))),--(ISNUMBER(MATCH(1&
H1:H13,D1:D3&E1:E3,0))),I1:I13)
 
B

Bob Phillips

Just looked at the worksheet on ExcelForum and it is different to that you
posted on VBAExpress, so it need to be changed to this

=SUMPRODUCT(--(ISNUMBER(MATCH(G1:G15&"Yes",A3:A7&B3:B7,0))),--(ISNUMBER(MATC
H(H1:H15&"Yes",D3:D5&E3:E5,0))),I1:I15)

Reply from Microsoft Public Newsgroups, not ExcelForum

--
HTH

Bob Phillips

Bob Phillips said:
=SUMPRODUCT(--(ISNUMBER(MATCH(1&G1:G13,A1:A5&B1:B5,0))),--(ISNUMBER(MATCH(1&
H1:H13,D1:D3&E1:E3,0))),I1:I13)

--
HTH

Bob Phillips

wezred said:
I posted this up on VBA Express but have not had any reply from them. I
was wondering if I could get some help here.

Please see attached file. I'm trying to use arrays to sum a group of
numbers based on 2 variables which is selected on a separate cell.

I realised that it only works with 1 variable. I'm trying to make it
work with about 5 variable. Please see attached file. You can see that
it says #N/A if I tried to do this with 2 variables.
Click to expand...
=SUM(IF(($G$3:$G$15=TRANSPOSE((IF($B$3:$B$7="Yes",$A$3:$A$7))))*($H$3:$H$15=
Click to expand...
 
O

olasa

Here's one solution
=SUMPRODUCT((LOOKUP(G3:G15,A3:B7)="Yes")*(LOOKUP(H3:H15,D3:E5)="Yes")*I3:I15)

The only requirement is that ReferenceTable 1 and 2 must be sorted
Ascending.

Hope it helped
Ola Sandström


Enclosed file:
http://www.excelforum.com/attachment.php?attachmentid=3631&stc=1


+-------------------------------------------------------------------+
|Filename: arra.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3631 |
+-------------------------------------------------------------------+
 
Top