Complicated IF and COUNTIF for a bakery spreadsheet

Hi there,

I was helped out earlier on this forum but have started a new thread as this is a slightly different issue.

I have a Bread_subs sheet and a Delivery sheet. I have a formula on the delivery sheet which pulls together data from the Bread_subs sheet including customer name, type of bread and delivery day.

What this fails to do, is make sure the person's subscription is up to date.
The data for bread subscriptions is in columns G and H of the Bread_subs page.

For the formulas in the Production boxes I used this bit of code to only count the bread if it was within the right dates (some people have bread orders which start in September)

=SUMIFS(I12:I149,K12:K149,"Wed",J12:J149,"WS",G12:G149,"<="&D1,H12:H149,">="&D2)

At the moment the formula on the DELIVERY sheet doesn't include this.

Is there a simple way of including it so?

Many thanks!

I would add a helper column to Bread Subs with formula in O12:

Formula:

Please Login or Register  to view this content.

copied down.

then revise formula in Delivery A3 to:

Formula:

Please Login or Register  to view this content.

Note: The order of appearance on the Delivery sheet will not be necessarily in alphanumeric order as per column N in Bread_subs sheet.

It's blank because the date range is not in the required date range.... so I purposely ignored it... isn't that your requirement?

Try:

Formula:

Please Login or Register  to view this content.

for OO you will have to replace all commas with semi-colons..... (i am not sure if all the other functions are compatible)

Did the formula on the first page work? it has COUNTIFS which is also only post 2003 compliant.

You can replace with this:

=IF(AND(G12<=$D$1,H12>=$D$2),LEFT(N12)&"_"&SUMPRODUCT((LEFT(N$12:N12)=LEFT(N12))*(G$12:G12<=$D$1)*(H$12:H12>=$D$2)),"")

copied down.

Not sure if SUMPRODUCT is liked by OO ???

According to http://wiki.openoffice.org/wiki/Docu...ed_by_category it seems all the functions in my formulas exist in oO too.

I am surprised that COUNTIFS works... I couldn't find evidence that the function exists in OO....

Great, you are welcome.