Γενική φόρμουλα
=SUMPRODUCT(--(range1=criteria),range2)
Περίληψη
Για να αθροίσετε ή να αθροίσετε στήλες με βάση κριτήρια σε παρακείμενες στήλες, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στη συνάρτηση SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο J5 είναι:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Εξήγηση
Στον πυρήνα, αυτός ο τύπος χρησιμοποιεί το SUMPRODUCT για να πολλαπλασιάσει και μετά να αθροίσει προϊόντα δύο συστοιχιών: array1 και array2 . Η πρώτη σειρά, πίνακας1 έχει συσταθεί για να ενεργεί ως «φίλτρο» για να επιτρέπει μόνο τις τιμές ότι τα κριτήρια πληρούν.
Το Array1 χρησιμοποιεί ένα εύρος που ξεκινά στην πρώτη στήλη που περιέχει τιμές που πρέπει να πληρούν τα κριτήρια. Αυτές οι "τιμές κριτηρίων" βρίσκονται σε μια στήλη στα αριστερά και αμέσως δίπλα στις "τιμές δεδομένων".
Τα κριτήρια εφαρμόζονται ως μια απλή δοκιμή που δημιουργεί έναν πίνακα τιμών TRUE και FALSE:
--($B5:$H5=J$4)
Αυτό το κομμάτι του τύπου "δοκιμάζει" κάθε τιμή στον πρώτο πίνακα χρησιμοποιώντας τα κριτήρια που παρέχονται και, στη συνέχεια, χρησιμοποιεί ένα διπλό αρνητικό (-) για να εξαναγκάσει τις τιμές TRUE και FALSE που προκύπτουν σε 1 και 0. Το αποτέλεσμα μοιάζει με αυτό:
(1,0,0,0,1,0,1)
Σημειώστε ότι το 1s αντιστοιχεί στις στήλες 1,5 και 7, οι οποίες πληρούν τα κριτήρια του "A".
Για τον πίνακα 2 στο εσωτερικό του SUMPRODUCT, χρησιμοποιούμε ένα εύρος που μετατοπίζεται από μία στήλη προς τα δεξιά. Αυτό το εύρος ξεκινά με την πρώτη στήλη που περιέχει τιμές για το άθροισμα και τελειώνει με την τελευταία στήλη που περιέχει τιμές για το άθροισμα.
Έτσι, στον τύπο παραδείγματος στο J5, αφού συμπληρωθούν οι πίνακες, έχουμε:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Δεδομένου ότι το SUMPRODUCT έχει προγραμματιστεί ειδικά για να αγνοήσει τα σφάλματα που προκύπτουν από τον πολλαπλασιασμό των τιμών κειμένου, ο τελικός πίνακας μοιάζει με αυτό:
(1,0,0,0,1,0,1)
Οι μόνες τιμές που "επιβιώνουν" πολλαπλασιασμού είναι αυτές που αντιστοιχούν στο 1s εντός του πίνακα1 . Μπορείτε να σκεφτείτε τη λογική στον πίνακα 1 "φιλτράρισμα" των τιμών στον πίνακα2 .
