PDA

View Full Version : [SOLVED] Array formula?



plasteredric
10-04-2017, 02:19 PM
20572

Hi all, I'm trying to figure out a formula for whats shown in the pic. Selections are all from the list at the top.

Need a formula to sum the row based on the values in the top grid.

Thanks all

offthelip
10-04-2017, 03:31 PM
I can't work out what you want from what you have given us, but have you looked at the functions sumif, sumifs, match and index, some combination of those should be able to sum anything from that table.

plasteredric
10-04-2017, 04:38 PM
I have tried those, just cant get my head around it.

The cells AH23:AL32 will contain either LAY, LSHOT, NAP, NB, STD. Each one has a value assigned based on the numbers in the same column on rows 8 to 12.
I need a formula which can sum all of those together, so the result in cell E23 would be 0.55 and in E24 it would be 0.5



I can't work out what you want from what you have given us, but have you looked at the functions sumif, sumifs, match and index, some combination of those should be able to sum anything from that table.

offthelip
10-05-2017, 02:24 AM
I think there are two ways that you can do that, firstly write your own VBA UDF function to do it, secondly, do it in two stages, by creating a "helper" array which converts the text into numbers. you can put this on a hidden sheet if necessary, use this formula to convert the text to numbers:

=INDEX(AH$8:AH$12,MATCH(AH23,$Y$8:$Y$12,0))

if you copy it across the columns it should be Ok.

plasteredric
10-05-2017, 05:11 PM
Looks like I have sorted it with this

=SUM(SUMIF(AH23:AL23,"LAY",AH8:AL8),SUMIF(AH23:AL23,"LSHOT",AH9:AL9),SUMIF(AH23:AL23,"NAP",AH10:AL10),SUMIF(AH23:AL23,"NB",AH11:AL11),SUMIF(AH23:AL23,"STD",AH12:AL12))

Anyone know if it can be neatened up a bit?

cheers

offthelip
10-06-2017, 01:27 AM
You will need to add some $ to that as you copying to the other rows to ensure the addressing of AH8:AL8, AH9:aL9 , etc remains absolute as you copy it down the rows (AH$8:AL$8 and AH$9:AL$9) etc

Well done

shrivallabha
10-06-2017, 02:18 AM
You can also try below and see if it works for you. In cell where you've employed formula mentioned in post #5 paste following formula and then copy down.
=SUMPRODUCT(($AH$8:$AL$12)*($Y$8:$Y$12=AH23:AL23))

plasteredric
10-08-2017, 03:41 AM
Thank you very much, this worked a treat


You can also try below and see if it works for you. In cell where you've employed formula mentioned in post #5 paste following formula and then copy down.
=SUMPRODUCT(($AH$8:$AL$12)*($Y$8:$Y$12=AH23:AL23))