Hi, I have this spreadsheet that I need to calculate how many editions of a picture are being printed. I need column Q (no.) to look at the artist ref (column B) and count how many of those have been printed previously and return the next sequential number - subtracting from 999 (column P).
Does anybody know a formula that can create this?
Thanks
Nikki
This file will show in column Q the sequence number for each unique print ref in column B.
I don't understand the part about subtracting from 999. Where do you want the result of that subtraction?
I love it when it is something so simple - thank you!
Can you help as to why the worksheet 'order form' is not returning the relevant columns from 'Print Orders' I do have a ROW INDEX MATCH formula in there but cannot get it to work for some reason
Thanks so much for your help
Sorry I've just thought it should only be counting the images if they are Limited Editions, so either only if column O says L/E or if column P says 999...
Hi, is anybody able to help with this? Please!
You have 2 problems that I can see:
1. The formulas on Order Form are looking for a match to the number of rows in the order form. So the first row is looking for a 1, second row would look for a 2, etc. The col containing the match is X on Print Orders. That column contains only 29. So the formulas in column X are wrong. X4 should be a 1, X5 a 2, etc.
Replace formula in X4 of Print orders with:
=IF(A4='Order Form'!$M$13,COUNTIF(A$4:A4,'Order Form'!$M$13),"")
Drag down
2. The formulas on Order form have been dragged across and have changed the reference to col X on Print Orders because you don't have $ signs in front of the X's in A19. Each formula in row 19 of Order Form should refer to col X of Print Orders
Replace formula in A19 of Order Form with:
=IF(ROWS($A$53:$A53)>$A$17,"",INDEX('Print orders'!B4:B171,MATCH(ROWS($A$53:$A53),'Print orders'!$X4:$X333,0)))
Drag across and down
Last edited by Cutter; 01-24-2011 at 11:55 AM.
perfect, thank you cutter!
Do you know how I can count images only if the are Limited Editions (so column O says L/E - can change this to Limited Editions if easier)
At the moment I have a =COUNTIF($B$4:$B5,B5) in column Q which counts how many of each print are being produced, the problem is I only want it to count the ones that are Limited Editions..
This is my final hurdle then I'm done, thanks for your help
I assume you are using a version prior to Excel 2007?
it's 2008 but I do use a mac - are you unable to open?
I'm not familiar with 2008 so I don't know if it can use COUNTIFS().
Try this:
=SUMPRODUCT(($B$3:$B4=B4)*($O$3:$O4="L/E"))
Placed in Q4 and dragged down
If you can use this one it is preferable:
=COUNTIFS($B$3:$B4,B4,$O$3:$O4,"L/E")
In Q4 and dragged down
Last edited by Cutter; 01-24-2011 at 12:36 PM.
Hi, this works great assuming that the same image isn't used as an open print also. For Instance, column B7 is the same print as B6 but this one is an 'Open' edition not 'L/E' but it is still counting this as 1? I've attached the spreadsheet for it to make more sense
nearly there!
Thank you for your help
In post #7 you stipulated counting if col says L/E.
If I understand correctly you now want to count if it says L/E or Open?
Judging by header it would appear that cells in col M will be restricted to L/E, Open or blank
If this is correct then you can change formula in O5 to:
=SUMPRODUCT(($B$4:$B5=B5)*($M$4:$M5<>""))
and drag down
Last edited by Cutter; 01-24-2011 at 02:18 PM.
I do only want it to count if column M is L/E, the problem is that it is also counting if column B code is repeated for both Open and L/E
So this code works fine =SUMPRODUCT(($B$4:$B5=B5)*($M$4:$M5<>"")) except that it also counts the same image in column B as a 1
for instance:
COLUMN B COLUMN M COLUMN O
SM002 L/E 1
SM002 Open 1 (this should be 0 or preferably blank as it is Open)
see it is also returning a 1 for that same code in column B - SM002 even though 1 of them is open and the other L/E? I only want it to count if L/E
OK, I think I see what you want now - try this in O5 and drag down:
=IF(M5="L/E",SUMPRODUCT(($B$4:$B5=B5)*($M$4:$M5="L/E")),"")
Although this is preferable if it's available to you (you never said)
=IF(M5="L/E",COUNTIFS($B$4:$B5,B5,$M$4:$M5,"L/E"),"")
BTW - why no Robert Bateman or Carl Brenders?
Last edited by Cutter; 01-25-2011 at 10:52 AM.
that's exactly what I'm looking for thank you! The second formula did work also.
I can get those artists on our books if you are interested in purchasing their work! check out our gallery artists here www.artandescape.com
will give you a great discount for all the help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks