SUMIFS avec date et nom de critères... le mois et l'année seulement

Je suis en train d'essayer d'obtenir un SUMIFS formule pour vérifier une colonne de dates et de la somme uniquement les valeurs qui correspondent à la correspondance de l'année et le mois, le critère de date. Je voudrais aussi que cela SUMIFS d'inclure un critère nom ainsi que la date. c'est à dire

La Cellule A1: =SUMIFS('Sheet1'!O:O, 'Sheet1'!D:D, 'Sheet2'!DATE(B2), 'Sheet1'!E:E, "Name")

la feuille sheet1 la colonne O est où la somme des valeurs sont stockées
la feuille sheet1 la colonne D est l'endroit où les valeurs de date sont stockées
feuil2 la cellule B2 est l'endroit où la date critère de comparaison est stocké
la feuille sheet1 la colonne E est l'endroit où les noms sont stockés
"Nom" est le nom de critère que je veux pour la somme de sélection

Tout insight sera le plus apprécié!

OriginalL'auteur Zane | 2015-02-17

  1. 4

    Vous peut utilisation SUMIFS si vous créez un début et une date de fin pour vos dates, c'est à dire avec cette version

    =SUMIFS('Sheet1'!O:O,'Sheet1'!D:D, ">="&EOMONTH('Sheet2'!B2,-1)+1, 'Sheet1'!D:D, "<"&EOMONTH('Sheet2'!B2,0)+1, 'Sheet1'!E:E, "Name")

    EOMONTH est utilisée pour obtenir le début et dates de fin du mois, alors votre SUMIFS sommes les valeurs correctes en fonction de vos autres critères.

    Si B2 est garanti d'être le premier du mois, vous pouvez omettre le premier EOMONTH fonction

    Ah ne pense même pas à l'aide d'une fourchette de dates
    Compte tenu de a) d'environ 3× efficacité de SUMIFS sur SUMPRODUCT b) la capacité d'utiliser l'ensemble des références de colonnes et c) la capacité de heureux de sauter sur la non-conformité des valeurs de jeter SUMPRODUCT en conniptions c'est le préférais largement de la méthode.
    Avec les formules de tableau vous pouvez accomplir des points b et c. Et pas que je suis en désaccord que SUMIFS est probablement plus rapide, mais comment savez-vous de l'efficacité de chaque formule?
    J'ai exécuter des tests de vitesse de sumproduct vs sumif, sumifs, nb.si et countifs avec le même nombre de critères identiques à la fois avec l'ensemble de la colonne de référencement dans sumproduct et avec sumproduct limitée à l'étendue des données (les autres restant à l'ensemble de la colonne de référencement). Sumproduct, au mieux, prend 3 fois plus de temps pour compléter un cycle de calcul.
    merci pour le modifier et d'utiles commentaires appréciés

    OriginalL'auteur barry houdini

  2. 0

    Solution avec SUMPRODUCT

    Je trouve ça plus simple à utiliser SUMPRODUCT dans des situations de ce genre.

    Pour aucun en-tête de rangée, vous pouvez simplement utiliser:

    =SUMPRODUCT((MONTH(Sheet1!D:D)=MONTH(Sheet2!$B$2))*(YEAR(Sheet1!D:D)=YEAR(Sheet2!$B$2))*(Sheet1!E:E="Name"),Sheet1!O:O)

    Il suffit de remplacer "Name" avec ce que vous voulez.

    Si vous avez une ligne d'en-tête (ou si la colonne contient des valeurs qui ne sont pas des dates valides), vous devez utiliser une formule matricielle dans SUMPRODUCT:

    =SUMPRODUCT((IF(ISERROR(MONTH(Sheet1!D:D)),Sheet1!D:D,MONTH(Sheet1!D:D))=MONTH(Sheet2!$B$2))*(IF(ISERROR(YEAR(Sheet1!D:D)),Sheet1!D:D,YEAR(Sheet1!D:D))=YEAR(Sheet2!$B$2))*(Sheet1!E:E="Name"),Sheet1!O:O)

    (Formules de tableau sont saisis à l'aide de ctrl + shft + enter)

    Ok, toujours pas de chance. Voici le nouveau code que j'ai utilisé comme vous l'avez suggéré (Ive renommé depuis des draps et déplacé quelques colonnes autour. '=SUMPRODUCT((MONTH(SupportCalls!F:F)=MONTH(Calculations!B3))*(YEAR(SupportCalls!F:F)=YEAR(Calculations!B3))*(SupportCalls!E:E=K9),SupportCalls!P:P)'
    le message d'erreur indique qu'une "valeur de la formule est de se tromper de type de données"
    Vous recevrez un #VALEUR! d'erreur si vous appliquez MOIS ou à l'ANNÉE de la fonction pour une valeur de texte, donc, ça va se produire lorsque vous utilisez l'ensemble de la colonne et vous avez des valeurs d'en-tête dans la ligne 1 ou de tout autre texte, il est préférable d'utiliser la plage de dates spécifique - ou découvrez ma solution à l'aide SUMIFS - d'une manière générale pour être plus efficace

    OriginalL'auteur chancea