In tab 1, column "A" of my report i have a list of vendor numbers (some repeat). Column E has the amount spent on a particular order. On tab 2 i have a separate report that lists each vendor once by the vendor number in column J. I need to get the total count for each order to correspond with each vendor in tab 2, column Z along with the spend in column AA.
I need an if statement i am assuming similir to =if(a2:a1000(tab1)= j2Count) or =if(a2:a1000(tab1)=j2(sum(e2)
Try
=SUMIF(Sheet1!$A$2:$A$1000, J2, Sheet1!$E$2:$E$1000)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Use COUNTIF
=COUNTIF(A2:a1000,J2)
Life's a spreadsheet, Excel!
Say thanks, Click *
So i think i left something off, as none of those are working. On tab 2, in my column "J" the vendor numbers go from J2:J284.
Okay, in rereading your original post,
In Z2 of tab 2, copied down
=COUNTIF(sheet1!A2:a1000,J2) (ace's formula)
this counts how many times the vender appears in tab 1
In AA2 of tab 2, copied down
=SUMIF(Sheet1!$A$2:$A$1000, J2, Sheet1!$E$2:$E$1000)
this sums up the $ amounts in column E of sheet 1 for the vender mentioned in J2
You'll need to modify sheet names (if they have spaces, they need single quotes around them) and the data ranges
If this is not working, perhaps a sample workbook (Go Advanced>Manage Attachments)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks