Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
criteria
=SUMPRODUCT(--(D13:D328=1),--(E13:E328=1),F13:F328)
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation. Hth, Merjet |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=DMIN(database,field,criteria) question about criteria | Excel Discussion (Misc queries) | |||
Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
have input box. Need criteria entered to refer to cond. format criteria | Excel Programming |