vlookup and countif?

**thompsy121** · 09-25-2008, 08:14 AM

Hi All,

As you can see there are multiple sales advisors with different amounts of sales and with dates processed and outstanding sales that need processed (they are the ones without dates). I need a few formulas to find out how many sales each advisor has with dates, and how many they have outstanding without dates. Also another cell which tells you the amount they are processed (with dates). Hope this makes sense.

I have tried countif's, lookups the lot but cant seem to get my head around it. Lookup doesnt work for me becasue the advisors will not always be in ascending order. Countif also does not work because it will just tell me how many sales an "individual advisor" has, but again i need them without the dates as well.

thanks

**arthurbr** · 09-25-2008, 08:26 AM

Hi and welcome to the board
Depending on what you need a Pivot Table is a possible solution

HTH

**sweep** · 09-25-2008, 08:27 AM

Hi,

In the example you posted,

B18=SUMIF(A2:A13,"Bob",B2:B13)

B20=SUMPRODUCT((A2:A13="Bob")*(C2:C13<>"")*(B2:B13))

B21=SUMPRODUCT((A2:A13="Bob")*(C2:C13="")*(B2:B13))

**thompsy121** · 09-25-2008, 08:32 AM

hi,

thanks for that, never thought of that, once i have produced the pivot table, can it be hidden and can i copy individual results to other cells in the spreadsheet.

thanks

**sweep** · 09-25-2008, 08:37 AM

or

Count Bob & with dates =SUMPRODUCT((A2:A13="Bob")*(C2:C13<>""))

Count Bob & without dates =SUMPRODUCT((A2:A13="Bob")*(C2:C13=""))

**thompsy121** · 09-25-2008, 08:44 AM

thanks for the formulas they work great. didn't even think abouyt sumif's

**thompsy121** · 09-25-2008, 10:04 AM

Right guys,

I have used your formulas and i must admit im impressed. Now i have another question that may be quite tricky.

In the cells i have highlighted yellow - the formula counts the non-blank cells. Obviously these will be date only fields i will input. Which is fine, however what if i add text into these cells. I then want it to not count that cell - example if i enter cancelled(as in file).

Any advice on this one.

Thanks

**sweep** · 09-25-2008, 10:49 AM

You can test to see if it's a number (which is how excel stores dates). In your example,

I28=SUMPRODUCT((A1:A100="Tom A")*(ISNUMBER(C1:C100)))

**thompsy121** · 09-25-2008, 11:12 AM

Thanks for that, worked a treat. Coming along great now

**thompsy121** · 09-26-2008, 02:34 PM

Hi could some please comment on this one for me please. Please see attached file.

1. If I delete a date in column C or enter text, below the number reduces by one (Apps on MLD). I.e. it is no longer being counted.

2. I now need this to happen to columns O and R.

Example in row 2 - Bob A has an application of £250, returned on 29/9, with MPPI and HC.

If i deleted the date in C2, below in my table the results would change, as the condition has not been met, therefore cells O and R would not count them either.

This works great using the ISNUMBER.

However, when I enter text I.e (Cancelled) in cell C2, cell J24 removes one sale, however O24 and R24 stay the same.

Help on this would be really appreciated.

Thanks

**protonLeah** · 09-26-2008, 06:34 PM

I haven't followed the previous threads, but the formulas below require that the dates be entered/formatted as text.
In K2:

Please Login or Register  to view this content.

In O2:

Please Login or Register  to view this content.

and, in R2

Please Login or Register  to view this content.

I replaced your team initials with the REPLACE() fn only to be able to drag-fill the formulas

**thompsy121** · 09-27-2008, 05:43 AM

Thanks for that, everything is working how i need it now.

Thanks everyone

**thompsy121** · 09-27-2008, 11:04 AM

Hi i need one last bit of help on this one.

Please see attached file and comment.

Column N shows the total amount of sales each advisor has, if I delete the date in column C for a certain advisor, beloew column N will automatically minus that amount. This also happens to the other columns which relate to that cell with the date deleted.

On the rest of my cells, i have it doing the same calculation, but this time when i add text in stead of a date ...in this case is want to enter cancelled. When it enter text into column C, the rest of my figures update as the formulas have been put in place. But column N shows the same value.

Here is my formula - =SUMPRODUCT((A6:A104="Tom A")*(C6:C104<>"")*(B6:B104))

In my other cells i have used the ISNUMBER(DATEVALUE in my formula. Can this be added to the above formula as well.

Thanks

**protonLeah** · 09-27-2008, 06:30 PM

TRy this formula mod:

Please Login or Register  to view this content.

**thompsy121** · 09-28-2008, 07:02 AM

Thanks for all your help, that worked a treat. I think it's finally complete now.

Many thanks again, great forum.

**thompsy121** · 09-28-2008, 11:52 AM

H All,

One final thing before it is complete. I now need to rank these figures to show which sales advisor has got the highest figures for certain cells. See attached file.

Any suggestions would be appreciated.

Thanks