CODEWITHSUNDEEP
excelexcel-formula
novicer
novicer

Reputation: 1

How to sum a column if a particular column is a weekday and another column contains specific names of a person

I am trying to figure out if there is a way to find total amount spent by each person using the table given beside it, provided excludes Sundays & Saturdays with the help of formulas.

PS: I tried adding another column with =IF(WEEKDAY(C3,2)<6,F3,0) in column G to find if it is a weekday and then return the amount only on weekday.

Then I used =SUMIF(D3:$D$31,"Doug",$G$3:$G$31) in I7 (Similar formula in I7:P7) to find the total sum for each person.

But I want to know if it is possible to do it without creating any additional columns (i.e. a combined formula in I7:P7 to find the total amount spent by each person on weekdays.) enter image description here

Upvotes: 0

Views: 171

Answers (1)

norie
norie

Reputation: 9857

You could use a SUMPRODUCT formula for this.

Put this in I7 and copy across.

=SUMPRODUCT($F$3:$F$31,--($D$3:$D$31=I$6),--(WEEKDAY($C$3:$C$31,2)<6))

Upvotes: 2

Related Questions