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

 

[Aladin Akyurek]

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

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

A conditional like

Details!$M$2:$M$8045=$A26

results in an array of logical values (like {TRUE,TRUE,FALSE,...}) which must be converted in numeric values as SumProduct requires.

=--TRUE ===> 1

=--FALSE ===> 0

 

[Tabs]

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

quote:
--------------------------------------------------------------------------------
Originally posted by Tabs:
...I have never seen the "--" characters in a formula before, what does that mean?...
--------------------------------------------------------------------------------



A conditional like

Details!$M$2:$M$8045=$A26

results in an array of logical values (like {TRUE,TRUE,FALSE,...}) which must be converted in numeric values as SumProduct requires.

=--TRUE ===> 1

=--FALSE ===> 0


So in the reply you initially posted, was I supposed to input the TRUE of FALSE, or would formula containing the "--" have calculated that. I am afraid that my level of understanding may be too basic for your help.

Thanks

tabs

 

[Aladin Akyurek]

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

quote:
--------------------------------------------------------------------------------
Originally posted by Tabs:
...I have never seen the "--" characters in a formula before, what does that mean?...
--------------------------------------------------------------------------------



A conditional like

Details!$M$2:$M$8045=$A26

results in an array of logical values (like {TRUE,TRUE,FALSE,...}) which must be converted in numeric values as SumProduct requires.

=--TRUE ===> 1

=--FALSE ===> 0


So in the reply you initially posted, was I supposed to input the TRUE of FALSE, or would formula containing the "--" have calculated that. I am afraid that my level of understanding may be too basic for your help.

Thanks

tabs

No, the posted formula does the needed conversion. For more on SumProduct, see:

http://www.mrexcel.com/board2/viewtopic.php?t=59063

 

[Tabs]

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

Thank you