À l'aide de SUMIFS avec plusieurs ET OU les conditions de

Je voudrais créer un succincte formule Excel qui calcule la SOMME d'une colonne basée sur un ensemble de conditions ET, en plus d'un ensemble de conditions OU.

Mon tableau Excel contient les données suivantes et j'ai utilisé les noms définis pour les colonnes.

  • Quote_Value (Feuille De Calcul!$A:$A) est titulaire d'une valeur comptable.
  • Days_To_Close (Feuille De Calcul!$B:$B) contient une formule qui entraîne un certain nombre.
  • Vendeur (Feuille De Calcul!$C:$C) contient du texte et un nom.
  • Quote_Month (Feuille De Calcul!$D:$D) contient une formule (=TEXTE(Feuille de calcul!$E:$E,"mmm-yy"))pour convertir une date/heure numéro dans une autre colonne dans un texte en fonction du mois de référence.

Je veux SOMME Quote_Value si le Vendeur est égal à JBloggs et Days_To_Close est égal ou inférieur à 90 et Quote_Month est égal à l'un des suivants (Oct-13 Nov-13, ou Déc-13).

Pour le moment, je n'ai eu que cela fonctionne, mais il comprend beaucoup de répétitions, qui je ne pense pas que j'ai besoin.

=SUM(SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Oct-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Nov-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Dec-13"))

Ce que je voudrais faire est de quelque chose de plus comme ce qui suit, mais je ne peux pas travailler la syntaxe correcte:

=SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,OR(Quote_Month="Oct-13",Quote_Month="Nov-13",Quote_Month="Dec-13"))

Que la formule ne fait pas d'erreur, elle retourne une valeur de 0. Pourtant, si je examiner manuellement les données, ce n'est pas correct. J'ai même essayé d'utiliser TRIM(Quote_Month) afin de s'assurer que les espaces n'avais pas glissée dans les données, mais le fait que mon extension de SOMME formule fonctionne indique que les données sont OK et que c'est un problème de syntaxe. Quelqu'un peut-il m'orienter dans la bonne direction?

OriginalL'auteur Dominic | 2013-11-27

  1. 19

    Vous pouvez utiliser SUMIFS comme ce

    =SUM(SUMIFS(Quote_Value,Salesman,"JBloggs",Days_To_Close,"<=90",Quote_Month,{"Oct-13","Nov-13","Dec-13"}))

    La SUMIFS fonction retourne un "tableau" de 3 valeurs (un total de chaque pour "Oct-13", "Nov-13" et "Déc-13"), si vous avez besoin SUM à la somme de ce tableau et de vous donner le résultat final.

    Être prudent avec cette syntaxe, vous pouvez avoir au plus deux critères dans la formule avec "OU" conditions...et si il y a deux puis en un, vous devez les séparer avec les critères de des virgules, dans l'autre avec des points-virgules.

    Si vous avez besoin de plus, vous pouvez utiliser SUMPRODUCT avec MATCH, par exemple dans votre cas

    =SUMPRODUCT(Quote_Value,(Salesman="JBloggs")*(Days_To_Close<=90)*ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"},0)))

    Dans cette version vous pouvez ajouter n'importe quel nombre de "OU" à l'aide de critères ISNUMBER/MATCH

    + 1 pour montrer deux méthodes 🙂
    Merci, ces deux méthodes de travail pour moi. Cependant, est-il un moyen de spécifier le mois de valeurs dans le tableau {"Oct-13","Nov-13","Déc-13"} à l'aide de références de texte stocké dans une autre cellule, e.g {Feuille de calcul!G2,Feuille De Calcul!H2,Feuille De Calcul!I2}?
    Jamais l'esprit, j'ai pensé à elle. J'ai remplacé ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Déc-13"}, avec ISNUMBER(MATCH(Quote_Month,G2:I2 où G2, H2 et I2 sont les cellules contenant du texte Oct-13 Nov-13 Déc-13
    C'est exact, vous pouvez utiliser une plage dans les deux SUMPRODUCT et SUMIFS versions. Si vous le faites dans le SUMIFS version la formule doit ensuite CTRL+MAJ+ENTRÉE, sauf si vous utilisez SUMPRODUCT en place de SOMME
    Juste tombé sur ce post. Étonnante technique! Personne ne sait pourquoi/comment cela fonctionne? Comme, j'ai ce MATCH(Quote_Month,{"Oct-13","Nov-13","Déc-13"},0) est utilisé pour produire un vecteur. Pourquoi cela fonctionne même est un mystère pour moi. Quand j'ai foiré autour avec cela, le plus évident première étape pour moi était de compter le nombre de visites, c'est à dire la SOMME du vecteur. Il ne fonctionne pas. Le fx assistant montre clairement le vecteur, et je peux voir les résultats. La SOMME ne sera pas la somme. Mais SUMPRODUCT va -pourquoi? Retour à SUMIFS, je peux voir que le {} la notation des résultats, mais pas de n'importe quelle gamme de référence (par exemple G2:I2) - pourquoi? Excel est bizarre.

    OriginalL'auteur barry houdini

  2. 2

    Vous pouvez utiliser BDSOMME, qui sera plus souple. Si vous souhaitez modifier le nom du Vendeur ou de la Citation du Mois, vous ne devez pas modifier la formule, mais seulement quelques-uns des critères les cellules. Veuillez voir le lien ci-dessous pour plus de détails...Même les critères peuvent être la formule à copier à partir d'autres feuilles de

    http://office.microsoft.com/en-us/excel-help/dsum-function-HP010342460.aspx?CTT=1

    OriginalL'auteur mooneazy

  3. 1

    Vous pourriez envisager de référencement de la réelle date/heure dans la colonne source, pour Quote_Month, ensuite, vous pouvez transformer votre OR dans un couple de ANDs, quelque chose comme (assuing la date est dans quelque chose que j'ai choisi d'appeler Quote_Date)

    =SUMIFS(Quote_Value,"<=90",Quote_Date,">="&DATE(2013,11,1),Quote_Date,"<="&DATE(2013,12,31),Salesman,"=JBloggs",Days_To_Close)

    (J'ai déplacé la des conditions intéressantes pour l'avant).

    Cette approche fonctionne ici parce que "OU" condition est en fait de spécifier une plage de dates - il pourrait ne pas fonctionner dans d'autres cas.

    OriginalL'auteur Mike Woodhouse

  4. 1

    Quote_Month (Feuille De Calcul!$D:$D) contient une formule (=TEXTE(Feuille de calcul!$E:$E,"mmm-yy"))pour convertir une date/heure numéro dans une autre colonne dans un texte en fonction du mois de référence.

    Vous pouvez utiliser OR en ajoutant + dans Sumproduct. Voir ce

    =SUMPRODUCT((Quote_Value)*(Salesman="JBloggs")*(Days_To_Close<=90)*((Quote_Month="Cond1")+(Quote_Month="Cond2")+(Quote_Month="Cond3")))

    Capture d'écran

    À l'aide de SUMIFS avec plusieurs ET OU les conditions de

    Je ne comprends pas quel est le problème, mais quand j'entre dans cette formule, je reçois un #VALEUR! erreur disant que l'une des valeurs de formule est de type de données incorrect.
    Pas sûr 🙂 jusqu'à ce que je vois de vos données. Si vous avez remarqué, ça fonctionne pour moi.

    OriginalL'auteur Siddharth Rout

  5. 1

    Vitesse

    SUMPRODUCT est plus rapide que SUM tableaux, c'est à dire avoir {} tableaux dans le SUM fonction. SUMIFS est 30% plus rapide que SUMPRODUCT.

    {SUM(SUMIFS({}))} vs SUMPRODUCT(SUMIFS({})) les deux fonctionne très bien, mais SUMPRODUCT se sent un peu plus facile d'écrire sans le CTRL-SHIFT-ENTER pour créer le {}.

    Préférence

    Personnellement, je préfère écrire SUMPRODUCT(--(ISNUMBER(MATCH(...)))) sur SUMPRODUCT(SUMIFS({})) pour de multiples critères.

    Toutefois, si vous avez un menu déroulant où vous souhaitez sélectionner des caractéristiques spécifiques ou tous les, SUMPRODUCT(SUMIFS()), est la seule façon d'aller. (pour sélectionner "tous", la valeur doit entrer en "<>" + "Quel que soit le mot que vous voulez tant que ça ne fait pas partie des caractéristiques spécifiques".

    OriginalL'auteur Walter S

  6. 0

    Afin d'obtenir la formule de travail, placez le curseur à l'intérieur de la formule et appuyez sur ctrl+maj+entrée et ensuite, il sera!

    OriginalL'auteur user3685392

  7. 0

    Avec le suivant, il est facile de lier l'adresse de la Cellule...

    =SUM(SUMIFS(FAGLL03!$I$4:$I$1048576,FAGLL03!$A$4:$A$1048576,">="&INDIRECT("A"&ROW()),FAGLL03!$A$4:$A$1048576,"<="&INDIRECT("B"&ROW()),FAGLL03!$Q$4:$Q$1048576,E$2))

    Pouvez utiliser l'adresse /substitut /Colonne fonctions nécessaires à l'utilisation de Cellules adresses en pleine DYNAMIQUE.

    OriginalL'auteur Ganesh