Γενική φόρμουλα
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Περίληψη
Για να συνοψίσουμε εάν τα κελιά περιέχουν είτε μια συμβολοσειρά κειμένου είτε μια άλλη (δηλ. Περιέχουν "γάτα" ή "αρουραίος") μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT μαζί με το ISNUMBER + SEARCH ή το FIND. Στο παράδειγμα που εμφανίζεται, ο τύπος στο κελί F5 είναι:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
που επιστρέφει το άθροισμα των τιμών σε C4: C8 όταν τα κελιά στο B4: B8 περιέχουν είτε "cat" είτε "rat".
Εξήγηση
Όταν αθροίζετε τα κελιά με κριτήρια "Ή", πρέπει να είστε προσεκτικοί για να μην κάνετε διπλή μέτρηση όταν υπάρχει πιθανότητα και τα δύο κριτήρια να επιστρέψουν αληθινά. Στο παράδειγμα που δείχνεται, θέλουμε να αθροίσουμε τιμές στη στήλη C όταν τα κελιά στη στήλη Β περιέχουν είτε "cat" είτε "rat". Δεν μπορούμε να χρησιμοποιήσουμε SUMIF με δύο κριτήρια, επειδή το SUMIFS βασίζεται στη λογική AND. Και αν προσπαθήσουμε να χρησιμοποιήσουμε δύο SUMIFS (δηλαδή SUMIFS + SUMIFS) θα διπλασιάσουμε γιατί υπάρχουν κελιά που περιέχουν τόσο "cat" όσο και "rat".
Αντ 'αυτού, χρησιμοποιούμε έναν τύπο όπως αυτό:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Αυτός ο πυρήνας αυτού του τύπου βασίζεται στον τύπο που εξηγείται εδώ που εντοπίζει κείμενο μέσα σε ένα κελί με ISNUMBER και SEARCH:
ISNUMBER(SEARCH("text",range)
Όταν δοθεί μια περιοχή κελιών, αυτό το απόσπασμα θα επιστρέψει έναν πίνακα τιμών TRUE / FALSE, μία τιμή για κάθε κελί το εύρος. Σε αυτόν τον τύπο, χρησιμοποιούμε αυτό το απόσπασμα δύο φορές, μία φορά για "γάτα" και μία για "αρουραίος", οπότε θα έχουμε δύο πίνακες. Σε αυτό το σημείο, έχουμε:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Στη συνέχεια, προσθέτουμε αυτές τις συστοιχίες μαζί, επειδή η προσθήκη χρησιμοποιείται σε δυαδική άλγεβρα για λογική OR. Η μαθηματική λειτουργία εξαναγκάζει αυτόματα τις τιμές TRUE και FALSE σε 1s και 0s, οπότε καταλήγουμε στον παρακάτω πίνακα:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Κάθε αριθμός σε αυτόν τον πίνακα είναι το αποτέλεσμα της προσθήκης TRUE και FALSE τιμών στις δύο αρχικές συστοιχίες μαζί. Στο παράδειγμα που εμφανίζεται, ο πίνακας μοιάζει με αυτό:
(2;0;2;1;0)
Πρέπει να προσθέσουμε αυτούς τους αριθμούς, αλλά δεν θέλουμε να διπλασιάσουμε. Επομένως, πρέπει να βεβαιωθούμε ότι οποιαδήποτε τιμή μεγαλύτερη από το μηδέν μετράται μόνο μία φορά. Για να το κάνουμε αυτό, επιβάλλουμε όλες τις τιμές να TRUE ή FALSE ελέγχοντας τον πίνακα με "> 0". Αυτό επιστρέφει TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Στη συνέχεια μετατρέπουμε σε 1/0 χρησιμοποιώντας ένα διπλό αρνητικό (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
και τελικά:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
Το SUMPRODUCT πολλαπλασιάζει τα αντίστοιχα στοιχεία των δύο συστοιχιών μαζί και αθροίζει το αποτέλεσμα, επιστρέφοντας 70.
Επιλογή με διάκριση πεζών-κεφαλαίων
Η συνάρτηση SEARCH αγνοεί την περίπτωση. Εάν χρειάζεστε μια ευαίσθητη επιλογή, αντικαταστήστε το SEARCH με τη λειτουργία FIND.
