Array Help

[putnum]

I have attached the spreadsheet for those who can help.

If you scroll to the right you will be on the correct place in the sheet.(Starting with I1)


Ok one more thing and then I am done!

I built a "Amount Checker" into my budget and what I need it to do is this.

I type in a Order Number in I6 and it finds all the numbers that match from the Order column (J4:J100) than makes sure that the amount equals the Bank Amount that does not have a category or tag assigned to it and than in I8 it will say Correct or Check Again if it fails.

Example:

I type 2 in I6 and it find all the entries that start with 2 and add them and then makes sure it matches the -$40.00 because it does not have a category or tag assigned. If i need to be more clear just let me know.

I have included some example code that I used for something similiar but it does not work for this example. (I know why it doesn't just don't know how to fix it for my above problem.

=IF(I6="Blank",IF(SUM(G2:G1000)=SUM(ABS(H2:H1000)),"Blank","Blank"),IF(AND(SUMIF(B2:B1000,A4,G2:G1000)=SUM((B2:B1000=A4)*(IF(H2:H1000<0,-1,1))*H2:H1000),SUMIF(B2:B1000,A4,G2:G1000)>0),"Correct","Check Again"))

[rwgrietveld]

better to use a filter for this in combination with Subtotal.

Anyhow. I think you should re-engineer your Workbook.

If you would like to do data manipulation as you state, you need a proper database layout, tables where each row is a record and each column is an atribute.

In you case:
Column A & I should not be there.
B:G is a table
J:O is a table

[putnum]

I know I could use tables and all that but I am simply just wanting a array function that does it. I know it can be done because I had seen it before. I just can't remember how to do it.

[putnum]

Here is the copy of the array working in a little diffrent setting.

Called the finger print checker in a4

[putnum]

I am bumping this up. Can anybody help with this one?

[putnum]

ok I got it down to this code but still can't figure all of it out? Can somebody please help!!

=IF(I6="Blank",SUM(L3:L43),SUMIF(J3:J43,I6,L3:L43))

[putnum]

I was able to solve this using the below code. Took me awhile but I did it yea!! Happy New Year everybody.

=SUMPRODUCT((J4:J1000=I6)*(M4:M1000<>"")*(L4:L1000))