Γενική φόρμουλα
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Περίληψη
Για να μετρήσετε με πολλά κριτήρια, συμπεριλαμβανομένης της λογικής για ΔΕΝ ένα από πολλά πράγματα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT μαζί με τις συναρτήσεις MATCH και ISNA.
Στο παράδειγμα που εμφανίζεται, ο τύπος στο G8 είναι:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Όπου το "φύλο" είναι η ονομαζόμενη περιοχή C4: C12 και η "ομάδα" είναι η ονομαζόμενη περιοχή D4: D12.
Σημείωση: Το MATCH και το ISNA επιτρέπουν στον τύπο να κλιμακωθεί εύκολα για να χειριστεί περισσότερες εξαιρέσεις, καθώς μπορείτε εύκολα να επεκτείνετε το εύρος ώστε να συμπεριλάβετε επιπλέον τιμές "NOT".
Εξήγηση
Η πρώτη έκφραση στο εσωτερικό του SUMPRODUCTS δοκιμάζει τιμές στη στήλη Γ, Φύλο, έναντι της τιμής στο F4, "Male":
(gender=F4)
Το αποτέλεσμα είναι ένας πίνακας τιμών TRUE FALSE όπως:
(TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE)
Όπου το TRUE αντιστοιχεί στο "Male".
Η δεύτερη έκφραση του SUMPRODUCTS ελέγχει τιμές στη στήλη D, Group, έναντι των τιμών στο G4: G5, "A" και "B". Αυτό το τεστ αντιμετωπίζεται με MATCH και ISNA ως εξής:
ISNA(MATCH(group,G4:G5,0))
Η συνάρτηση MATCH χρησιμοποιείται για να ταιριάζει με κάθε τιμή στην ονομαζόμενη περιοχή "ομάδα" έναντι τιμών στο G4: G5, "A" και "B". Όπου το παιχνίδι πετύχει, το MATCH επιστρέφει έναν αριθμό. Όταν το MATCH αποτυγχάνει, το MATCH επιστρέφει # N / A. Το αποτέλεσμα είναι ένας πίνακας ως εξής:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Δεδομένου ότι οι τιμές # N / A αντιστοιχούν στο "not A or B", το ISNA χρησιμοποιείται για την "αντιστροφή" του πίνακα σε:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Τώρα το TRUE αντιστοιχεί στο "όχι A ή B".
Μέσα στο SUMPRODUCT, τα δύο αποτελέσματα πίνακα πολλαπλασιάζονται μαζί, το οποίο δημιουργεί έναν μοναδικό αριθμητικό πίνακα μέσα στο SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
Στη συνέχεια, το SUMPRODUCT επιστρέφει το άθροισμα 2, που αντιπροσωπεύει "2 άντρες που δεν ανήκουν στην ομάδα Α ή Β".
