# Thread: Puzzled with Formula (Excel 2003)

1. ## Puzzled with Formula (Excel 2003)

hi there
in the attached sheet i need to enter a total in cell E22 on the( invoice sheet)
the question i need to ask is.......IF (invoice sheet cell B22) MATCH's (contact details sheet, P7:P46) then the cost is (contact detailssheet J8) but if (contact details sheet L7) is checked then the cost is at reduced rate of (course details sheet K8)..........now somewhere in that function i need to determin how many clients are on the WT1561 course to find the total, total cost for the invoice.........is this possible or am i being over complicated.

2. ## Re: Puzzled with Formula (Excel 2003)

hi there i tried to do what you suggested but i counldnt get it to work, so i linked the check box cells to the cell next to them individually then used the formula you gave me with the ajustment of the new true false cells and it still doesnt work can you have another look.
kitty

3. ## Re: Puzzled with Formula (Excel 2003)

<P ID="edit" class=small>(Edited by rory on 20-Jan-08 19:19. Split formula onto 2 lines to avoid scrolling effect - Rory)</P>Hi KittyCatt,

Try this formula:
=SUMPRODUCT(IF(B22=Contact_Details!\$P\$7:\$P\$46,1,0) ,IF(Contact_Details!\$L\$7:\$L\$46=FALSE,1,0))*IF(Invo ice!B22=Course_Details!C8,Course_Details!J8,0)+
SUMPRODUCT(IF(B22=Contact_Details!\$P\$7:\$P\$46,1,0), IF(Contact_Details!\$L\$7:\$L\$46=TRUE,1,0))*IF(Invoic e!B22=Course_Details!C8,Course_Details!K8,0)

A couple of this to take note of:
- It is an array formula, so when you copy this and paste it into your sheet you must confirm entry with CTRL+SHIFT+ENTER.

- The check boxes on your Contact_Details Sheet must be linked to the cell they are in for the formula to work. How to do this:
* Right click the check box and choose Format Control. In the dialog, click in Cell Link and type the cell reference of the cell containing the check box. choose OK. Alternative is to delete the check box and use a Validation drop down to enter TRUE or FALSE in the cell. The formula is dependant on the value TRUE/FALSE in the L column to work.

4. ## Re: Puzzled with Formula (Excel 2003)

<P ID="edit" class=small>(Edited by rory on 20-Jan-08 19:20. Split formula onto 2 lines to avoid scrolling)</P>First off, a formula is unable to read the CheckBoxes on your "contact_Details" sheet. A simple solution is set the linked cell for each to the appropriate cell (this will put true/false in the cell when checked/unchecked.

It can be done manually. But this macro will do it for all of them (in J and L): [The macro should only have to be run once]
<pre>Option Explicit
Dim cb As CheckBox
For Each cb In ActiveSheet.CheckBoxes
Next
End Sub</pre>

If I understand this should give what you want. In INVOICE!E22:
=IF(B22="","",SUMPRODUCT((Contact_Details!\$P\$7:\$P\$ 51=B22)*(Contact_Details!\$L\$7:\$L\$51+(1-Contact_Details!\$L\$7:\$L\$51)*(1-Course_Details!\$K\$6))*
INDEX(Course_Details!\$J\$8:\$J\$12,MATCH(B22,Course_D etails!\$C\$8:\$C\$12,0))))

Copy it down the column.

It is complicated and creates an array with whether the Contact_Details value in Col P matches the value in Invoice!col B. For matches 1 is added for the TRUE values in Col L and 40% (=1-Coursedetails!K6) is added. It then multiplies the sum of these values times the looked up value in Col J of Details for that course code.

If there is no value in Invoice col B it puts a null string (to make it look blank). If there is no matching course code in course details it will give a #NA error. If there is a course detail but there is no data in contact details it will report a zero (0).

Steve

5. ## Re: Puzzled with Formula (Excel 2003)

This is an array based formula. You need to confirm entry of the formula when you exit the cell by pressing CTRL+SHIFT+ENTER.
If you want, you can hide the M column so that the TRUE/FALSE is not visible in the Contact_Details.

PS: I notice that my results are different to Steves results. I would recommend that you check the results to ensure that the formulas supplied are calculating correctly.

6. ## Re: Puzzled with Formula (Excel 2003)

sorted now thanks for your help i hadnt changed column p to q so thanks for pointing that out
kitty

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•