1. ## Lookup nightmere (2003)

Hi,

I am in a real mess here now! I am trying to get a table to count the number of a coloum where the next coloum = a set day of week

i.e.

In the cell coloum Mon and row 1 I want it to lookup all the matching data from the table to the left of the sheet and give me a count of patient group where patient group = 1 and DOW = Mon

Many thanks

I have attached a cut down sample

2. ## Re: Lookup nightmere (2003)

Enter this formula in F2:

=SUMPRODUCT((\$A\$2:\$A\$21=\$E2)*(LEFT(\$B\$2:\$B\$21,3)=L EFT(F\$1,3)))

Fill down to F11, then fill right to column L.

3. ## Re: Lookup nightmere (2003)

Hi Hans,

Thanks for that, it worked great but... I forogt to mention something...

The is an extra column in the data table called breach, I only want them previous request to be counted if the value in column breach is equal to 1

Many thanks

4. ## Re: Lookup nightmere (2003)

You should be able to extend the formula to include the extra condition.

5. ## Re: Lookup nightmere (2003)

Sorry Hans

I'm been at bit stupid, but I cant get the extra condition to work

Can you help, many thanks

6. ## Re: Lookup nightmere (2003)

There's no column called Breach in the sample workbook that you posted... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

7. ## Re: Lookup nightmere (2003)

=SUMPRODUCT((\$A\$2:\$A\$21=\$F2)*(LEFT(\$B\$2:\$B\$21,3)=L EFT(G\$1,3)),(\$D\$2:\$D\$21=\$E2)*(LEFT(\$B\$2:\$B\$21,3)=L EFT(G\$1,3)))

Many thanks

8. ## Re: Lookup nightmere (2003)

You don't have to repeat the condition LEFT(\$B\$2:\$B\$21,3)=LEFT(G\$1,3):

=SUMPRODUCT((\$A\$2:\$A\$21=\$F2)*(LEFT(\$B\$2:\$B\$21,3)=L EFT(G\$1,3))*(\$D\$2:\$D\$21=\$E2))

9. ## Re: Lookup nightmere (2003)

Thank Hans,

How do I get this same idea to work but give an average.

Thanks

10. ## Re: Lookup nightmere (2003)

The general idea is

=AVERAGE(IF((condition1)*(condition2)*(condition3) ,data))

as an array formula, i.e. confirm the formula with Ctrl+Shift+Enter. The number of conditions can vary.

11. ## Re: Lookup nightmere (2003)

Hi thanks hans, that works great.

On some of the cells I get #DIV/0! where there is no data in the source table. The data changes daily so the forumla needs to always be there but is there a way of making #DIV/0!
display as -

maybe somesort of error handler

Thanks

12. ## Re: Lookup nightmere (2003)

In general, if you have a formula
<code>
=EXPRESSION
</code>
that might return an error value, you can suppress it completely by changing the formula to
<code>
=IF(ISERROR(EXPRESSION),"",EXPRESSION)
</code>
or you can display a custom value:
<code>
=IF(ISERROR(EXPRESSION),"-",EXPRESSION)</code>

#### Posting Permissions

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