Solved: Criteria needing to match several items

[Melinda]

Hi,

I have tried to create the function myself but with no success. Can someone help me?

I need an function that will sum the items in column L on the SAP download sheet that match the following:
(1) Match the item in Column A on the Grants, Project, Phase sheet with the items in Column B on SAP download sheet,
(2) column C on the SAP download sheet has a 1 as the first number.
and
(3) that the date in column K on the SAP download sheet is prior or equal to the date in cell B1 on the Grants, Project, Phase sheet.

Thanks for any suggestion on how to accomplish getting this done.

[lucas]

Moved to what I assume is the correct forum since you reference cell B1 on certain sheets........


Originally posted in the announcements forum.

[Melinda]

Lucas,thanks. I am sorry. I didn't realize that I did that.

[Bob Phillips]

Try

=SUMPRODUCT(--(ISNUMBER(MATCH(SAP!B2:B20,'Grants, Project, Phase'!A2:A10,0))),
--(SAP!K2:K20<='Grants, Project, Phase'!B1),SAP!L2:L20)

[Melinda]

xld,

That didn't seem to work. It is pulling some tremendous amount even on grants that have only 5 lines.

Melinda

[Bob Phillips]

Can you post the workbook, as it is a bit like fumbling in the dark.

[Melinda]

Xld,

I thought I had attached a file to the original post but somehow it didn't work earlier. But here it is. Thanks for taking the time to figure this out for me.

[Melinda]

xld,

For some reason the file failed to load last time. However, I made it smaller so that I could attach it this time.

Thanks for all of your help.

Melinda

[Bob Phillips]

I think this is what it should be

=SUMPRODUCT(--('SAP download'!$B$2:$B$132='Grants,Project,Phase'!A2),
--('SAP download'!$K$2:$K$132<='Grants,Project,Phase'!$B$1),'SAP download'!$L$2:$L$132)

but they all come out at 0!

[Melinda]

xld,

You are on the right track the reason for it all of them coming to 0 is because the (2) condition was not met.

condition (2) says that to sum column C on the SAP download sheet that has a 1 as the first number.

Melinda

[Melinda]

xld,

Thank you very much for all of your help. I took what you had and modify it to include the 2 condition and it works.

This is the formula that worked:

=+SUMPRODUCT(--(LEFT('SAP download'!$C$2:$C$20000)="1"),--('SAP download'!$B$2:$B$20000=A154),--('SAP download'!$K$2:$K$20000<=$B$1),('SAP download'!$L$2:$L$20000))

Again, thank you very much for the help.

Melinda