PDA

View Full Version : Sum? sumif? sumproduct?



plasteredric
10-08-2017, 07:16 AM
I'm trying to figure out a formula to do what is shown in the pic if anyone can help.

thanks

20609

SamT
10-08-2017, 08:19 AM
That note in AA22 doesn't make sense.

There's no way to make any of those numbers in Z31:AF31 add up to 10.48/3

shrivallabha
10-08-2017, 09:15 AM
It will be lot better if you could upload the workbook instead of snapshot. For now, anyone wanting to help you needs to reconstruct the whole structure.

plasteredric
10-08-2017, 12:11 PM
20610
sample sheet attached, that should make it clearer
It will be lot better if you could upload the workbook instead of snapshot. For now, anyone wanting to help you needs to reconstruct the whole structure.

plasteredric
10-08-2017, 02:58 PM
I think i've figured out something that works for me.

=SUM((SUMIF(AH31:AH35,U9,AB31:AB35)*V9),(SUMIF(AH31:AH35,U10,AB31:AB35)*V10 ),(SUMIF(AH31:AH35,U11,AB31:AB35)*V11),(SUMIF(AH31:AH35,U12,AB31:AB35)*V12) )

Seems a bit messy though if anyone know a way of refining it.
Cheers

shrivallabha
10-08-2017, 09:36 PM
Following things are unclear to me
- Is this formula for one cell only?
- If not, can you demonstrate (manual) results for more than 1 columns or rows or whichever fashion you intend to implement? It will be easier to evaluate.

Above result can be achieved using following ARRAY formula (to be committed by using CTRL+SHIFT+ENTER simultaneously)
=SUM(((AB31:AB35)*TRANSPOSE(V9:V12))*ISNUMBER(SEARCH(AH31:AH35,TRANSPOSE(U9 :U12),1)))

plasteredric
10-09-2017, 05:42 PM
i've updated the sheet with a bit more data and added the formulas into row 16.

I tried the formula you posted which works to an extent, but it's picking the rows with blank cells and including them in the result.

Cheers

20621

Following things are unclear to me
- Is this formula for one cell only?
- If not, can you demonstrate (manual) results for more than 1 columns or rows or whichever fashion you intend to implement? It will be easier to evaluate.

Above result can be achieved using following ARRAY formula (to be committed by using CTRL+SHIFT+ENTER simultaneously)
=SUM(((AB31:AB35)*TRANSPOSE(V9:V12))*ISNUMBER(SEARCH(AH31:AH35,TRANSPOSE(U9 :U12),1)))

Aflatoon
10-10-2017, 01:45 AM
Maybe:

=SUMPRODUCT((AH$31:AH$35=TRANSPOSE($U$9:$U$12))*$AB$31:$AB$35*TRANSPOSE($V$ 9:$V12))

also array-entered.

shrivallabha
10-10-2017, 10:57 AM
i've updated the sheet with a bit more data and added the formulas into row 16.

I tried the formula you posted which works to an extent, but it's picking the rows with blank cells and including them in the result.

Cheers

20621
Here's SUM based one for copying across.
=SUM((($AB$31:$AB$35)*TRANSPOSE($V$9:$V$12))*ISNUMBER(SEARCH(TRANSPOSE($U$9 :$U$12),AH31:AH35,1)))

plasteredric
10-18-2017, 04:02 PM
Maybe:

=SUMPRODUCT((AH$31:AH$35=TRANSPOSE($U$9:$U$12))*$AB$31:$AB$35*TRANSPOSE($V$ 9:$V12))

also array-entered.

Worked great, is there a way of doing the same, but so that it calculates only visible(filtered) data?

Cheers

Aflatoon
10-19-2017, 12:31 AM
Add a column to the source data that uses SUBTOTAL on a column that is always populated (so it returns 1 for each visible row and 0 for hidden ones), then include that column in the multiplication.

plasteredric
10-19-2017, 04:42 PM
Right, i've added column Z with the formula "=- -SUBTOTAL(103,Y31)" in cells Z31:Z35
How do I then reference the column in this formula? "=SUMPRODUCT((AH$31:AH$35=TRANSPOSE($U$9:$U$12))*$AB$31:$AB$35*TRANSPOSE($V$ 9:$V12))"
I've tried a couple of ways but I cant get it to work
cheers


Add a column to the source data that uses SUBTOTAL on a column that is always populated (so it returns 1 for each visible row and 0 for hidden ones), then include that column in the multiplication.

Aflatoon
10-20-2017, 12:32 AM
It should be:

=SUMPRODUCT((AH$31:AH$35=TRANSPOSE($U$9:$U$12))*$Z$31:$Z$35*$AB$31:$AB$35*T RANSPOSE($V$9:$V12))