Sumifs

**mark909** · 03-31-2010, 07:32 AM

Hello,

Im trying to use SUMIFS but can get it to work. Would appreciate it if anyone tells me where im going wrong

=SUMIFS(CIVILS_LINE!D:D,CIVILS_LINE!C:C,"Micro-Trench",CIVILS_LINE!A:A,"DD0101A")

Im basically pointing it to a column containing individual lengths (D:D) and want to sum length if the information in C:C is "Micro-Trench" and the information in A:A is "DD0101A"

All i keep getting returned is a value of "0". Im quite new to this so not sure if im inputting the data wrong

Thanks for any help

**ratcat** · 03-31-2010, 07:50 AM

G'day Mark and welcome to the forum,

That looks fine too me.....but is the spelling matching in the formula to the working sheet.

Upload a dummy example if your still having troubles.

Cheers

RC

**mark909** · 03-31-2010, 08:33 AM

Spelling is fine. The files im querying are linked from oracle. Im not sure if this would affect the query

**DonkeyOte** · 03-31-2010, 09:04 AM

What does

=SUM(CIVILS_LINE!D:D)

return ?

If 0 the implication is such that you have numbers stored as text and need to either

a) coerce to numbers (run Text to Columns on CIVILS_LINE Col D and click Finish)

b) use an alternative (slower) method like SUMPRODUCT which can coerce as part of the process

Irrespective - avoid using entire column references - even with SUMIFS - though not as bad performance wise as SUMPRODUCT/Array it's still slow(ish) used over extended ranges.