Urgent-Novice needs help with writing a more complex formula

Tabs

New Member
Joined
Jan 15, 2004
Messages
8
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 :oops:
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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 :oops:

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

New Member
Joined
Jan 15, 2004
Messages
8
Re: Urgent-Novice needs help with writing a more complex for

Apologies this one was wrong!!
 

Tabs

New Member
Joined
Jan 15, 2004
Messages
8

ADVERTISEMENT

Re: Urgent-Novice needs help with writing a more complex for
Test.xls
BCDEFGHIJKLMN
1DATEWALLETNUMBERSACKNUMBERNUMBEROFCREDITSCREDITAMOUNTCUSTOMERNAMEOUTLETREFNO./SHOPNO.SORTCODEACCOUNTNUMBERBRANCHNAMEFAXNUMBERPRESENTATIONISSUECOMMENTS
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
1012/01/2004rmrsserviceusersfund200069Customer-LargeListing
1112/01/2004directlegalandcollection300163Customer-LargeListing
1212/01/2004abrsmmainaccount000706Customer-LargeListing
Details
 

Tabs

New Member
Joined
Jan 15, 2004
Messages
8
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
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

New Member
Joined
Jan 15, 2004
Messages
8
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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)
 

Tabs

New Member
Joined
Jan 15, 2004
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top