Extracting multiple results and adding them in separate columns

[Wizards]

Hi all,

I’ve attached a file with a sample of the data that I’m working with.

The data is in cells K3: U13.

I’m trying to produce a series of results in cells C3:F6.

In cells C4:F4 I have a working array function which looks in cells K3:U3 and searches for a value which matches what is in A1. Where it finds that value it will then return the value in cells K4:U4 with the same column number.

With a value of “S2” in cell reference A1, the first match will be found in cell reference O3. The value in row 4 and the same column number is $56.87 and so this is the value that is returned in cell C4.

I’m using an expanding reference so that I can copy the formula across to cell reference F4 and, in each successive column, the next occurrence of the value “S2” will be referenced. This all works well.

However, what I want to be able to do is supplement this formula so that it sums all the values in the referenced column where the value in the Category column matches the value in cell reference A2.

If I was successful in this formula then the value returned in cell reference C6 would have been 302.16; all the values in cell range O4:O13 where the corresponding value in cell range K4:K13 matches the value in cell reference A2.

I was expecting that I would use SUMPRODUCT to complete this formula but that is where I’m getting the error. Perhaps SUMPRODUCT isn’t the best function to use.
Any help to complete this would be appreciated.

[menem]

If you don't mind to use INDIRECT, please try this formula

Please Login or Register  to view this content.

Regards.

[Edward6266]

Try the following formular:
C6=SUMPRODUCT(SUMIF($K$4:$K$13,$A$2,OFFSET($M$4,,AGGREGATE(15,6,COLUMN($A:$H)/($N$3:$U$3=$A$1),COLUMNS($A:A)))))
Enter only, fill through right more 3 columns.

Hope it's correct!

:-)

[Wizards]

Hi Edward,

Once I changed the Offset from M4 to M3 it worked perfectly. Thanks.

[Wizards]

Hi Edward, further question.

I'm fairly new to the offset function and I've never used Aggregate before; if I want to change this to a sumifs formula because I want to use 2 criteria (Category and Unit), how would I go about it?

[Edward6266]

Hi Edward,

Once I changed the Offset from M4 to M3 it worked perfectly. Thanks.

You're welcome!
I think no need to change from M4 to M3, because the function Sumif() begin from the row 4 (K4), so it need to be balance at M4.

Regards

[Edward6266]

Hi Edward, further question.

I'm fairly new to the offset function and I've never used Aggregate before; if I want to change this to a sumifs formula because I want to use 2 criteria (Category and Unit), how would I go about it?

Can you give a sample of criteria you mentioned above.

Thanks

[Wizards]

It's exactly the same sample as provided previously but also using the range M3:M13 for criteria. For example, I would want to sum those in column O where the criteria 4000 and WA were satisfied.

[Edward6266]

Hi Edward, further question.

I'm fairly new to the offset function and I've never used Aggregate before; if I want to change this to a sumifs formula because I want to use 2 criteria (Category and Unit), how would I go about it?

I guess the following formular is what you required:
Assume that A3="WA"

Please Login or Register  to view this content.

Enter only.


Good night.
p/s: I can not upload any attachment for your reference.

[Wizards]

Thanks again - works well.