Trouble with proper syntax for SUMIFS

[JRay0108]

Alright so I've already gotten some help but I'm a little stuck again.

Here is my current forumula:
=IF($A9<$AU$2," ",SUMIFS(GDS!$AA:$AA,GDS!$E:$E,$A9,GDS!$P:$P,AI$5))

Column AA are prices but occasionally the value of the cell is "CLOSED".
I need that to pull over but instead it pulls a $O since it's "summing".
It was recommended I use SUMIFS instead of a VLOOKUP since there was duplicate data in the lookup column.
Other than not pulling the text I need it's working perfectly.
I'm thinking I need to add an addtional IF statement but couldn't get it to work.
The values in the cells I'm looking up are always either a dollar amount or the word CLOSED.

[FDibbins]

it might be easier to help if we could see what you are working with? please upload a sample workbook

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[JRay0108]

excelhelp.xlsx

Please see attchd example.
Columns refernce are the same.

Current equation is:
=IF($A25<$AU$2," ",SUMIFS(GDS!$AA:$AA,GDS!$E:$E,$A25,GDS!$P:$P,AI$5))

[jeffreybrown]

In what cell does this formula go?

Are you looking up the price by product and date from the main sheet and passing it to the GDS sheet?

How does the word "closed" end up in the cell?

[FDibbins]

I added a helper column on sheet2 AB with this...=E2&P2
then used this in your table on sheet1...
=INDEX(GDS!$E$1:$AB$10,MATCH(MAIN!$A2&MAIN!AI$1,GDS!$AB$1:$AB$10,0),MATCH("Price",GDS!$E$1:$AB$1,0))

in your example, it looked like you were taking the 1st instance if there were duplicates

[JRay0108]

Jeff,

I'm tryin to get the data to do to the MAIN tab from GDS.
My formula would be in cell AI2 of MAIN and in the other cells around it as well.
CLOSED in on the GDS tab which is a CSV file that is emailed to me periodically.
The MAIN tab is pulling in from about 10-12 other CSV and excel files.
The attachment is just an example.

[JRay0108]

I can't modify the GDS tab.
It's something that i have set up to just paste in the data from a CSV file to pull over to the MAIN tab.
If I have to modify my supporting tabs it kind of defeats the purpose.

[FDibbins]

just because you have to copy data from a CVS file onto that sheet, doesnt mean you cant still have that formula off to the side out of the way somewhere?

[JRay0108]

Would the formula be on the MAIN tab?
I must have missed it in your last response.
I've only used the forum a few times and don't fully understand the attachment part of things.

[FDibbins]

no, it would still be on the 2nd sheet, but im thinking that this would be a "blank" sheet, so you could put the formulas wayyy off to the side and just leave them there?

i was also playing around with a pivot table which will give you what you want. however, it will only work if ALL of your data columns have a heading, so not sure if this is an option either