formula (perhaps sumif?)

R

redb

Hi! In my workbook:
Column A = the days of the week - Monday, Tuesday, etc. (but Monday may be
listed from A1:A10, and Tuesday from A11:A15)
Column B = Date (of Monday, Tuesday, etc)
Column C = Job Titles (text)
Range D:T contain numerical values

I want to write a formula that sums all the values in column D that also
contain Monday in column A and a specific value in column C. Is there any
formula out there that would accomplish this? I've tried SUMIF, but it isn't
working for me; I may be writing it wrong. Any ideas?
 
B

bj

many people use sumproduct in this type of problem
=sumproduct(--(A1:A1000="Monday"),--(C1:C1000=criteria),D1:D1000)
the arrays in each section must be the same size but can't be the shorthand
for full columns [A:A wont work]
the --() makes the logic true false become numeric 1 0
 
P

PeterAtherton

redb said:
Hi! In my workbook:
Column A = the days of the week - Monday, Tuesday, etc. (but Monday may be
listed from A1:A10, and Tuesday from A11:A15)
Column B = Date (of Monday, Tuesday, etc)
Column C = Job Titles (text)
Range D:T contain numerical values

I want to write a formula that sums all the values in column D that also
contain Monday in column A and a specific value in column C. Is there any
formula out there that would accomplish this? I've tried SUMIF, but it isn't
working for me; I may be writing it wrong. Any ideas?
Click to expand...

Try something like


=SUMPRODUCT(--($A$1:$A$18="Mon")*($C$1:$C$18=C1)*($D$1:$D$18))

Peter
 
B

BenjieLop

redb said:
Hi! In my workbook:
Column A = the days of the week - Monday, Tuesday, etc. (but Monda
may be
listed from A1:A10, and Tuesday from A11:A15)
Column B = Date (of Monday, Tuesday, etc)
Column C = Job Titles (text)
Range D:T contain numerical values

I want to write a formula that sums all the values in column D tha
also
contain Monday in column A and a specific value in column C. Is ther
any
formula out there that would accomplish this? I've tried SUMIF, but i
isn't
working for me; I may be writing it wrong. Any ideas?
Click to expand...

Try this ...

*=sumproduct((A1:A100="Monday")*(C1:C100="whatever specific value yo
choose")*(D1:D100))
 
Top