Sumproduct or vba

[Laylow]

Hi All,
Wonder if anyone can help.
What I would like to do is to lookup the code in column A and return the result in column B.
I want to sum all values in column F when the code in column A has the following condition correct.
(i) Value in D is less than 2009012
(ii) Code in column A matches code in column E
(iii) Column G is either ATP or TTP. [Do not count all other types e.g. SSP]
For example for MAST01 result is 0 and MAYL01, result is 26,720.
Regards
Laylow

A B
CODE RESULT
MABE01
MABE02
MAST01
MAYL01



Column D ColumnE columnF columnG
PERIOD CODE VALUE TYPE
2008006 MABE01 40000 ATP
2008012 MABE01 45000 ATP
2009010 MABE01 15000 TTP
2008006 MABE01 50000 MMP
2008011 MABE01 50000 SSP
2009006 MABE02 50000 MMP
2010007 MABE02 50000 ATP
2011001 MABE02 110000 ATP
2008003 MABE02 55000 TTP
2011003 MABE02 55000 ATP
2010012 MAST01 14000 MMP
2009009 MAST01 43200 SSP
2010005 MAST01 27800 SSP
2008011 MAYL01 26720 ATP
2009005 MAYL01 37810 SSP
2011006 MAYL01 37810 TTP
2007012 MAYL01 62500 MMP

[teylyn]

This can be done with a sumproduct() or possibly other formulas, but ...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Laylow]

Hi Teylyn,

Thanks a bunch for your speedy response
Searched FAQ and couldn't find how to attach files.
Can you please help?
Regards
Laylow

[royUK]

Start a new answer, click Advanced & scroll down to find the Manage Attachments button

[Laylow]

Hi All,
Thanks to Teylyn and RoyUK for your response.

What I would like to do is to lookup the code in column A and return the result in column B. I have munaully populated column B's result the way it should look.
I want to sum all values in column F when the code in column A has the following conditions correct.
(i) Value in D is less than 2009012
(ii) Code in column A matches code in column E
(iii) Column G is either ATP or TTP. [Do not count all other types e.g. SSP]
For example for MAST01 result is 0 and MAYL01, result is 26,720.
I used a sumproduct similar to this =SUMPRODUCT(('Ledger Balance'!A:A<$N$1)*('Ledger Balance'!B:B=Summary!B126),('Ledger Balance'!C:C)) but this only works on two scenarios and not 3 or more.
Attached is an example of what I would like.

Regards
Laylow

[StephenR]

Here is one approach:

=SUMPRODUCT(($D$2:$D$18<2009012)*($E$2:$E$18=A2)*(($G$2:$G$18="ATP")+($G$2:$G$18="TTP")),$F$2:$F$18)

[Laylow]

Stephen R and the Rest.

God Bless you all.

I hope I can also contribute in this forum going forward.

Problem solved

Regards

Laylow