Array SUMIF formula to sum if numbers but it not place text

[jed38]

Okay so im gonna try to discribe my problem as best as I can.
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.

This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. Is this possible??
here is my formula
=SUM(IF((ProjectInfo!$AE$2:$AE$307 =$B59)*(ProjectInfo!$AD$2:$AD$307= AA$10),ProjectInfo!$AF$2:$AF$307,""))
projectInfo is the sheet where the list is.
Column AE is the Item column on the projectinfo sheet
column B on the summary sheet would be the item that i want to sum the quantities for
Column AD is the section location column on the projectinfo sheet
the AA$10 is the row and column of the section location i want to limit the sum of the quantities to
the formula is in cell AA59
So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.

Any one have any ideas on getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.


EDIT:
Column AF on the ProjectInfo Sheet is the quantity column

[daddylonglegs]

If you only expect one row to match try using a LOOKUP formula like this

=IFERROR(LOOKUP(2,1/(ProjectInfo!$AE$2:$AE$307=$B59)/(ProjectInfo!$AD$2:$AD$307=AA$10),ProjectInfo!$AF$2:$AF$307),"No match")

that will return a number or a text value, whatever is in the relevant cell

[jed38]

i will have multiple rows match which is why i have the SUM at add the quantities. A lump item will always have a LUMP in the Quantity I would want to know if its in that location at that point. but i need it mixed in with the SUM IF formula so i can handle any item

[daddylonglegs]

OK try this version

=IF(COUNTIFS(ProjectInfo!$AF$2:$AF$307,"Lump",ProjectInfo!$AE$2:$AE$307,$B59,ProjectInfo!$AD$2:$AD$307,AA$10),"Lump",SUMIFS(ProjectInfo!$AF$2:$AF$307,ProjectInfo!$AE$2:$AE$307,$B59,ProjectInfo!$AD$2:$AD$307,AA$10))

[jed38]

Figured it out
Array formula below

=IF((ProjectInfo!$AE$2:$AE$307=$B59)*(ProjectInfo!$AF$2:$AF$307="LUMP")*(ProjectInfo!$AD$2:$AD$307= AA$10),"LUMP",SUM(IF((ProjectInfo!$AE$2:$AE$307 =$B59)*(ProjectInfo!$AD$2:$AD$307= AA$10),ProjectInfo!$AF$2:$AF$307,"")))

[jed38]

Daddylonglegs
your also worked thanks for the help