SUMIF: Criteria range is from other sheet and problem occurs

M

mohitmahajan

I am trying to use "sumif" in a new workbook and the data range is fro
another sheet. This is creating a problem when the criteria is 5 o
multiple of 5 as the data comes incorrect or comes when there shoul
not be any data (in this case it should come 0 or NA or something).

Is this a problem with excel?

Pls guide!!!
 
F

Frank Kabel

Hi
is the range in your SUMIF formula in a different workbook
and is this workbook closed?. If yes SUMIF won't work for
this closed workbook. You may convert your SUMIFformula to
a SUMPRODUCT formula. e.g.
=SUMIF(lookup_range,condition,sum_range)
to
=SUMPRODUCT(--(lookup_range=condition),sum_range)

if this is not the reason for your error you may post your
existing formula.
 
M

mohitmahajan

Hi Frank,

I am not familiar with SUMPRODUCT, tried using it but couldn't work i
out. Sumif formula that I am using is : =SUMIF('[Laxm
Farswan.xls]DataDump'!$I:$J,13,'[Laxmi Farswan.xls]DataDump'!$I:$I)

Coloumn I contains the time taken to process one situation and coloum
J contains the day of the month represented by numerics which mean
that day 2 of the month will be represented by numeric 2.

Whenever I use this formula for this calculation, the data for the da
5 and its multiple come out incorrectly, pls guide
 
F

Frank Kabel

Hi
this formula looks strange for the first argument. try the following:
=SUMPRODUCT(--('[Laxmi Farswan.xls]DataDump'!$J1:$J1000=13),'[Laxmi
Farswan.xls]DataDump'!$I1:$I1000)
Note: sUMPRODUCT does not accept ranges like I:I. therefore I changed
them to I1:I1000
 
Top