# Urgent-Novice needs help with writing a more complex formula

#### Tabs

##### New Member
Hi there,

I don't know if anyone can help.....I am stuck at work, not been on this site before, and am needing to write a formula that will:

Count the results of a search for cells containing specific text, based on a specific date in the first column.

So I want to count the records of specific error types logged on a particular day.

To do this I think the formula has to find all the records written on the date selected, from the first column, and from the records selected find all the errors (text) of that particular type stored in another column.

I have been trying to use LOOKUP and COUNTIF together, but don't know how to link/embed these two functions together, and even if they are the right ones.

Anyone who can help me not have to stay here at work all night would be much appreciated.

Many thanks

Tabs

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Re: Urgent-Novice needs help with writing a more complex for

Welcome to the Board!

Can you post some of your sheet so everyone can see exactly what you're working with? Try using Colo's HTML Maker, the link to which can be found at the bottom of the page.

Smitty

Re: Urgent-Novice needs help with writing a more complex for

Tabs said:
Hi there,

I don't know if anyone can help.....I am stuck at work, not been on this site before, and am needing to write a formula that will:

Count the results of a search for cells containing specific text, based on a specific date in the first column.

So I want to count the records of specific error types logged on a particular day.

To do this I think the formula has to find all the records written on the date selected, from the first column, and from the records selected find all the errors (text) of that particular type stored in another column.

I have been trying to use LOOKUP and COUNTIF together, but don't know how to link/embed these two functions together, and even if they are the right ones.

Anyone who can help me not have to stay here at work all night would be much appreciated.

Many thanks

Tabs

A generic approach, pending Smitty's request to post some data,

=SUMPRODUCT(--(RANGE=DATE),--(RANGE="TEXT"))

Where date and text can be cells with the desired date and text you're searching for.

Re: Urgent-Novice needs help with writing a more complex for

Apologies this one was wrong!!

Re: Urgent-Novice needs help with writing a more complex for
Test.xls
BCDEFGHIJKLMN
212/01/200412/01/2004980600543995n/wBranch-NoDetailsonSackorManifest
312/01/200498060126428831055884Branch-NoDetailsonSackorManifest
412/01/200498060027896530133890Branch-NoDetailsonSackorManifest
512/01/200498060083877035722326Branch-NoDetailsonSackorManifest
612/01/2004londonboroughofsutton000000Customer-LargeListing
712/01/2004rexalsenate710028Customer-LargeListing
812/01/2004senateelectrical710271Customer-LargeListing
912/01/2004pitneybowesltd100115Customer-LargeListing
1112/01/2004directlegalandcollection300163Customer-LargeListing
1212/01/2004abrsmmainaccount000706Customer-LargeListing
Details

Re: Urgent-Novice needs help with writing a more complex for
Test.xls
ABCDEF
28
2912/01/200413/01/200414/01/200415/01/200416/01/2004
30Branch-Extra/MissingWalletinSack5489etc
31Branch-NoDetailsonSackorManifest3535etc
32Branch-NoManifest
33Branch-NoWalletno.onSack
Summary Sheet

Re: Urgent-Novice needs help with writing a more complex for

From a PM to help clarify:
Column B is obviously the date field, and Column M is the column I need to count specific error types from......on a selected date.

I am wanting to display this information that in a table, where in the vertical column you have a list of the error types, and across the top of the table, you have each day (one week in total). I was trying to use the dates in the cells across the top as a reference in the formula to select the date required in Column B.Using LOOKUP

I was then trying to use the COUNTIF formula, once the fields on the correct date were selected, to count the specific error type (Presentation issue - Column M) in the vertical list of the table.

Re: Urgent-Novice needs help with writing a more complex for

{=COUNT(IF(Details!\$M\$2:\$M\$8045='Summary Sheet'!\$A26,IF(Details!\$B\$2:\$B\$8045='Summary Sheet'!B\$25,Details!\$B\$2:\$B\$8045)))}

Hi Guys, thanks alot for your help, I just wanted to show you a formula that a work colleague came up with that worked, I would be interested in light of the help I got with a formula above, which is a better formula and why....that's if anyone has the time.

Thanks alot again

Sincerely

Tabs

Re: Urgent-Novice needs help with writing a more complex for

Tabs said:
{=COUNT(IF(Details!\$M\$2:\$M\$8045='Summary Sheet'!\$A26,IF(Details!\$B\$2:\$B\$8045='Summary Sheet'!B\$25,Details!\$B\$2:\$B\$8045)))}...

=SUMPRODUCT(--(Details!\$M\$2:\$M\$8045='Summary Sheet'!\$A26),--(Details!\$B\$2:\$B\$8045='Summary Sheet'!B\$25),Details!\$B\$2:\$B\$8045)

is twice faster and doesn't need control+shift+enter.

If you have this formula in 'Summary sheet', you can shorten it to:

=SUMPRODUCT(--(Details!\$M\$2:\$M\$8045=\$A26),--(Details!\$B\$2:\$B\$8045=B\$25),Details!\$B\$2:\$B\$8045)

Re: Urgent-Novice needs help with writing a more complex for

Thank you.

I have never seen the "--" characters in a formula before, what does that mean?

When I looked at that formula, I was assuming that I needed to enter something in place of the "--" and wasn't sure what. I am sorry if my questions seem stupid, although willing, I am definitely a novice.

Cheers

Tabs

Replies
2
Views
124
Replies
1
Views
233
Replies
1
Views
145
Replies
2
Views
374
Replies
1
Views
157

1,218,889
Messages
6,145,019
Members
450,586
Latest member
hehehihi2007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back