How to sum multiple columns with multitple conditions? I want to sum all the gro

Question

How to sum multiple columns with multitple conditions?

I want to sum all the groups that is equal to "CUSTOMER SERVICE" which is in column D, the data to add are in column E to AI (E8:AI507), there are 31 columns to add, this is an attendance report if you wanna know.

                                     M     T    W   TH   F   Sat Sun   M .. .. . . . . . .
SW MISCELLANEOUS 1:05 0:0 0:0 0:0 1:05 0:0 0:0   1:0
CUSTOMER SERVICE   1:05 0:0 0:0 0:0 1:05 0:0 0:0   1:0
SW DEMANDS           1:05 0:0 0:0 0:0 1:05 0:0 0:0   1:0

I want to add only the day that falls only on Monday(M) which is stated on E5:AI5.

=SUMIFS(January!E8:AI507,January!D8:D507,B88,January!E5:AI5,C77) ** this is not working

Thanks in advance!

Explanation / Answer

T^his :

=SUMPRODUCT(($D$8:$D$1507="Customer Service")*($E$5:$AI$5="Monday")*($E$8:$AI$1507))

#VALUE Error is given if there is a character in your sum range. If there is any character, this will be ignored by ,. But not by *.

It means that in your sum range some non numeric value is there which gets ignored by comma but * is not able to ignore.

Hence, formula can be written as

=SUMPRODUCT((January!D8:D507=B88)*(January!E5:AI5=C77),(January!E8:AI507))

Note - This fact is applicable only for SUMPRODUCT.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.