Excel Formula

Out of category
1

Hello

 

I have a request concerning a formula search on excel,

 

On the attached image, I have a list of deal numbers and I am looking to know the total of quotes won, pending or lost depending on the year...

 

I already have the formula to list the number of won for example, "NB.SI(Table2[Status]; "won")" I lack the integration of the data on the year of creation.

 

Thank you for your help.


capture2.png
1 Like
2

Hello

 

A condition of the following type is used:

 

=if(year(B2)="2013"; valeur_si_vrai; valeur_si_faux)

 

 

Where valeur_si_vrai is the basic formula (if the year is in column B).

 

Edit:

In fact, it doesn't work!

 

You should use:

 

=NB.SI. ENS(C:C;"won"; B:B; 2013)

 

if the status is in column C and the year in column B using the formula =year(cellule_où_se_trouve_la_date)

 

With this formula, it is possible to have as many nested "nb.si" as you want.

3 Likes
3

You would have to add a column with the formula =CONCATENATE(B1; YEAR(B2))

 

B1 being the cell with "won", "lost", ... and B2 the date of the quotation

 

This would give "won2013", "lost2012", ...

 

Then go back to the formula with =NB.SI(Table...; "won2013") and so on

1 Like
4

Perfect, both solutions work.

 

Thanks for the info...

 

By any chance if you have a support explaining the excel formulas I'm all for it...

 

 

5

There are many Excel formulas:

 

Clicking on the "fx" in the Excel edit line already allows a keyword search.

 

Otherwise, see here:

http://office.microsoft.com/fr-fr/excel-help/liste-des-fonctions-de-feuille-de-calcul-par-categorie-HP010079186.aspx

6

Yes indeed...

 

Thank you in TT cases for your help.

1 Like
7

Hello

 

A pivot table is simpler and fulfills the function perfectly. (see attached image)

 

S.B


tableau.png