criteria

[Guest]

This is a formula I am using
=sumif(D13328,1,F13:F328)
so I am checing if any cells from d13 to d328 have a 1 in them and if so to
total the sum of F13 to F328 ie d14,20 and 23 have a one in so sum f14,20 and
23. I want to add a check so I check for a 1 in d13 to d328 and e13 to e328
to then sum f13 to f328
this doesn't work =sumif(D13328andE13:E328,1,F13:F328)

 

[Guest]

Just do

=Sumif(D13:E328,1,F13:F328)

 

[Bernard Liengme]

Do you mean you want 1 in both the D and the E ranges?
Then use:
=SUMPRODUCT(--(D13328=1),--(E13:E328=1),F13:F328)
or
=SUMPRODUCT((D13328=1)*(E13:E328=1)*(F13:F328))

For details see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Guest]

If I've understood correctly try:-

=SUM((D13328=1)*(E13:E328=1)*F13:F328)

It'san array formula so enter with CTRL+Shift+Enter

 

[Bernie Deitrick]

Rob,

=SUMPRODUCT((D13328=1)*(E13:E328=1)*(F13:F328))

HTH,
Bernie
MS Excel MVP

 

[Guest]

=sumproduct(--(D13328=1),--(E13:E328=1),F13:F328)

 

[merjet]

=SUMPRODUCT(--(D13328=1),--(E13:E328=1),F13:F328)

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an
explanation.

Hth,
Merjet

 

[Guest]

Thanks Tom, this worked very well until I wanted to increase the amount of
different scenarios i.e. d13:d328=2 and e13:e328=1 f13:f328
then d13:d328=1 and e13:e328=3 f13:f328

My fault, I didn't put down that I would like to be able to mix and match
differnet scenarios.

Rob

 

[merjet]

Then use =SUMPRODUCT(--(D13328=A1),--(E13:E328=B1),F13:F328)
where A1 and B1 reference cells containing whatever values you want to
test.

Hth,
Merjet

 

[Guest]

you might also want to look at just creating a pivot Table report under the
data menu.