Urgent-Novice needs help with writing a more complex formula

[Tabs]

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

 

[Smitty]

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

 

[Brian from Maui]

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

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.

 

[Tabs]

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

Apologies this one was wrong!!

 

[Tabs]

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

Test.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	WALLETNUMBER	SACKNUMBER	NUMBEROFCREDITS	CREDITAMOUNT	CUSTOMERNAME	OUTLETREFNO./SHOPNO.	SORTCODE	ACCOUNTNUMBER	BRANCHNAME	FAXNUMBER	PRESENTATIONISSUE	COMMENTS
2	12/01/2004	12/01/2004	980600543995								n/w	Branch-NoDetailsonSackorManifest
3	12/01/2004		980601264288								31055884	Branch-NoDetailsonSackorManifest
4	12/01/2004		980600278965								30133890	Branch-NoDetailsonSackorManifest
5	12/01/2004		980600838770								35722326	Branch-NoDetailsonSackorManifest
6	12/01/2004					londonboroughofsutton	000000					Customer-LargeListing
7	12/01/2004					rexalsenate	710028					Customer-LargeListing
8	12/01/2004					senateelectrical	710271					Customer-LargeListing
9	12/01/2004					pitneybowesltd	100115					Customer-LargeListing
10	12/01/2004					rmrsserviceusersfund	200069					Customer-LargeListing
11	12/01/2004					directlegalandcollection	300163					Customer-LargeListing
12	12/01/2004					abrsmmainaccount	000706					Customer-LargeListing
Details

 

[Tabs]

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

Test.xls
	A	B	C	D	E	F
28
29		12/01/2004	13/01/2004	14/01/2004	15/01/2004	16/01/2004
30	Branch-Extra/MissingWalletinSack	5	4	8	9	etc
31	Branch-NoDetailsonSackorManifest	3	5	3	5	etc
32	Branch-NoManifest
33	Branch-NoWalletno.onSack
Summary Sheet

 

[Smitty]

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.

 

[Tabs]

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

 

[Aladin Akyurek]

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)))}...

=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)

 

[Tabs]

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

Hi Aladin,

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