Using IF AND to track quantity totals

[erice]

Hello,
I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?

My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?

I hope I explained this clear enough, if not let me know and I will try to ellaborate more.

Thanks,

Eric
Quantity Tracking.xlsx

[ARGK]

Hi
I would suggest removing the blank rows from the raw data first as follows:
1. Select the cells in one column from the top of your list to the bottom.
2. Make sure that all the blank cells in this selected range are the rows you want to delete.
3. Press the F5 key on your keyboard (or select Edit, Goto).
4. Click the Special... button.
5. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
6. Now choose Edit, Delete, select the Entire Row option and click OK.

Hope this helps.
Tony

[sktneer]

On the Totals Sheet, in B3 try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.
Is that something you can work with?

[sktneer]

Find the attached sheet to see if this takes care of your second query to show the records for Q only.

[erice]

Tony,
Thank you for the advice on how to remove the blank cells from my document. That drastically reduced the number of false results I was getting.

sktneer,
The formula that you provided seems to be working just fine, the only problem is that I do not understand what it is doing. Could you explain a little on what the IFERROR is doing as well as the INDEX.

Thanks,
Eric

[FDibbins]

IFERROR is an error trapping function that only activates if the formula inside it gives an error...
=IFERROR(your-formula,what-to-do-if-the-formula-gives-an-error)

in pre-2007 versions, you would use IF(ISERROR())
=IF(ISERROR(your-formula),what-to-do-if-the-formula-gives-an-error,what-to-do-if-the-formula-does-not-give-an-error)

INDEX is a function for pulling specified data from a table...
=index(criteria-to-find,row-to-search-in,column-to-search-in)....so table range,row-number,column-number