criteria
 

This is a formula I am using
=sumif(D13:D328,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(D13:D328andE13:E328,1,F13:F328)

criteria
 

Just do

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

"Rob" wrote:

This is a formula I am using
=sumif(D13:D328,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(D13:D328andE13:E328,1,F13:F328)

criteria
 

Do you mean you want 1 in both the D and the E ranges?
Then use:
=SUMPRODUCT(--(D13:D328=1),--(E13:E328=1),F13:F328)
or
=SUMPRODUCT((D13:D328=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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Rob" wrote in message
...
This is a formula I am using
=sumif(D13:D328,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(D13:D328andE13:E328,1,F13:F328)

criteria
 

If I've understood correctly try:-

=SUM((D13:D328=1)*(E13:E328=1)*F13:F328)

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

"Rob" wrote:

This is a formula I am using
=sumif(D13:D328,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(D13:D328andE13:E328,1,F13:F328)

criteria
 

Rob,

=SUMPRODUCT((D13:D328=1)*(E13:E328=1)*(F13:F328))

HTH,
Bernie
MS Excel MVP


"Rob" wrote in message
...
This is a formula I am using
=sumif(D13:D328,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(D13:D328andE13:E328,1,F13:F328)

criteria
 

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

--
Regards,
Tom Ogilvy

"Rob" wrote:

This is a formula I am using
=sumif(D13:D328,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(D13:D328andE13:E328,1,F13:F328)

criteria
 

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

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

Hth,
Merjet

criteria
 

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Rob" wrote:

This is a formula I am using
=sumif(D13:D328,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(D13:D328andE13:E328,1,F13:F328)

criteria
 

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

Hth,
Merjet

criteria
 

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

--
Regards,
Tom Ogilvy


"merjet" wrote:

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

Hth,
Merjet