Column A has dates.
B2 has Book Code like "001,002,003".
B3 has book code "006,008,005,009,001,002". So, maximum book codes in Column B is 6.
Column C has No. of Books.
Column D has Mode. Purchase is 1, Sent is 2.
If books are purchased, then 1 is entered in D, only one book code is entered in B and no. of books purchased is entered in C. More than 1 book is normally purchased.
But only one book per code is sent normally. So, if books are sent, then 2 is entered in D, maximum of 6 book codes or less are entered in B and one book per code is entered in C. eg. If "001,002,003" is entered in B2 then, 3 is entered in C2.
a2: 23/4/09 b2: 003 c2: 1 d2:2
a3: 24/4/09 b3: 002 c3: 1 d3: 2
a4: 23/4/09 b4: 001 c4: 5 d4: 1
a5: 23/4/09 b5: 007,009,008,006,001 c5:1 d5:2
Help me to find out how many books on each code was purchased and sent?
=SUMIF(C5:C8,301)
=sumif(range,criteria)
example
D2
Mode
1
1
2
1
2
=SUMIF(d2:D6,1)
=SUMIF(d2:D6,2)My database is like this:
Column a has Dates.
Column B has book codes.
Column c has qty. Rule for qty column: If books are sent only one copy of each book is sent. if books are purchased more than one copy can be purchased.
Column d has purchase or sent mode. 1 denotes purchase and 2 denotes sent.
E2 has beginning date of the month.
Column f has each row foe each book.
Column g has the formula given below by an Internet Friend Mr. Aladdin, which calculates the sent no of books for this month.
a2: 21/4/09 b2: 001,002 c2:1 d2: 2 e2: 1/4/09 f2: Bk 001 g2: 4
a3: 22/4/09 b3: 007,001,005 c3: 3 d3: 2 f3: Bk 002 g3: 3
a4: 23/4/09 b4: 002,004,003,009 c4: 4 d4: 2 f4: Bk 003 g4: 2
a5: 23/4/09 b5: 007,009,008,006,001 c5: 5 d5: 2
a6: 24/4/09 b6: 001 c6: 10 d6: 1
a7: 27/4/09 b7: 003,001,005,009,002,006 c7: 6 d7: 2
g2 has: =SUMPRODUCT(($A$2:$A$500>=DATEVALUE(MONTH($E$2)&"/1/"&YEAR($E$2)))*($D$2:$D$500=2)*ISNUMBER(FIND("001",$B$2:$B$500)))
g3 has :=SUMPRODUCT(($A$2:$A$500>=DATEVALUE(MONTH($E$2)&"/1/"&YEAR($E$2)))*($D$2:$D$500=2)*ISNUMBER(FIND("002",$B$2:$B$500)))
g4 has :=SUMPRODUCT(($A$2:$A$500>=DATEVALUE(MONTH($E$2)&"/1/"&YEAR($E$2)))*($D$2:$D$500=2)*ISNUMBER(FIND("003",$B$2:$B$500)))
It works!
Thanks a lot, Jupiter.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.