Searching data with 2 criteria

  • Thread starter Thread starter Jimmy Bench
  • Start date Start date
J

Jimmy Bench

Here is what I have a problem with.
{=SUM(IF(A200:A620=A9,IF(B200:B620="service",G200:G620,"No
Service Sales"),"No Service Sales"))}

The problem is with the first IF statement. I have it set
to =A9 and A9 is a user defined 6 digit number that then
is searched for in the existing database. The way it is
now i get a false answer which equals zero so my sum is
zero. Now, if I put the actual number in place of the A9
then the statements works fine. Below is how it does work
but not what I want, I want to be able to have the user
input a number and the statement works.
{=SUM(IF(A200:A620="221531",IF
(B200:B620="service",G200:G620,"No Service Sales"),"No
Service Sales"))"

Thanks for all the help
 
It seems A200:A620 is a text-formatted range, while your condition in A9 is
a genuine number...

{=SUM(IF(A200:A620=A9&"",IF(B200:B620="service",G200:G620,"No Service
Sales"),"No Service Sales"))}
 
Back
Top