Sum? sumif? sumproduct?

[plasteredric]

I'm trying to figure out a formula to do what is shown in the pic if anyone can help.

thanks

Capture1.jpg

[SamT]

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]

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]

vbaexpress sample.xlsm
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]

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]

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]

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

vbaexpress sample (1).xlsm

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]

Maybe:

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

also array-entered.

[shrivallabha]

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

vbaexpress sample (1).xlsm

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]

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]

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]

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]

It should be:

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