Calculation of Selection Group using Arrays (or VBA?)
 

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.

=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 suppose
to create any additional columns or cells. All formula must be in 1 cell
and must also work if each variable has 50 selections.

Thank you.


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

--
wezred
------------------------------------------------------------------------
wezred's Profile: http://www.excelforum.com/member.php...o&userid=25591
View this thread: http://www.excelforum.com/showthread...hreadid=390110

=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" wrote in
message ...

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.


=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 suppose
to create any additional columns or cells. All formula must be in 1 cell
and must also work if each variable has 50 selections.

Thank you.


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

--
wezred
------------------------------------------------------------------------
wezred's Profile:
http://www.excelforum.com/member.php...o&userid=25591
View this thread: http://www.excelforum.com/showthread...hreadid=390110

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" wrote in message
...

=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" wrote in
message ...

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.



=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 suppose
to create any additional columns or cells. All formula must be in 1 cell
and must also work if each variable has 50 selections.

Thank you.


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

--
wezred
------------------------------------------------------------------------
wezred's Profile:
http://www.excelforum.com/member.php...o&userid=25591
View this thread:
http://www.excelforum.com/showthread...hreadid=390110

Here's one solution
=SUMPRODUCT((LOOKUP(G3:G15,A3:B7)="Yes")*(LOOKUP(H 3: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...tid=3631&stc=1


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390110

Thank you. I think it seems to work for now. Appreciate the help.


--
wezred
------------------------------------------------------------------------
wezred's Profile: http://www.excelforum.com/member.php...o&userid=25591
View this thread: http://www.excelforum.com/showthread...hreadid=390110