Problems with SUMIF in Excel

[Navision]

=SUMIF(GLAcc;">='450'";Razdoblje1)-SUMIF(GLAcc;">='45999'";Razdoblje1)

it doesnot work on Excel 2003 because it doesnot taking first cell
"450" (text) at all.

operand >= doesnot work. Please help!

 

[Roger Govier]

Hi
Try
=SUMIF(GLAcc;">="&45000;Razdoblje1)-SUMIF(GLAcc;">="&46000;Razdoblje1)

You don't need to treat the GL codes as text.
I am assuming you want to total all GL codes in the 45000 range.
Your formula, (if it had worked) would have omitted 45999 from the sum.

 

[Navision]

Thanks,

but it doesnt work. This is an export file from MBS Navision plans and
it has predifined formulas in excel. And I can not change it in
Navision, maybe only in excel. Example:
Br. racuna GK Ime TC Šifra 1.1.2006 1.2.2006
TROŠKOVI POSLOVANJA 40000 72144 6548
TROŠKOVI PR & MARKETING I PRODAJE 40000 57828 5689
450 Troškovi oglašavanja 40000 5689 859
451 Troškovi PR-a 40000
452 Troškovi istraživanja tržišta 40000 568
453 Troškovi donacija i sponzorstva 40000 458
454 Troškovi tiska plakata, izrade oglasa 40000 5689
455 Troškovi izrade foršpana 40000 56234
456 Fotografiranje,animac.,jinglovi,logotip 40000
457 Institucij.mat.(vizitke,memo,posjet,vre) 40000
Ostali troškovi prodaje i marketinga 40000 0 0
4590 Ostali troškovi prodaje i marketinga 40000 568
4591 Troškovi nagradnih igara-prodaja 40000
4592 Troškovi spikiranja foršpana-Yammat 40000

450 is exported txt cell,blanks in first columns are formulas cell..

 

[Bob Phillips]

Is that data in many columns, or just one?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Thanks,

but it doesnt work. This is an export file from MBS Navision plans and
it has predifined formulas in excel. And I can not change it in
Navision, maybe only in excel. Example:
Br. racuna GK Ime TC Sifra 1.1.2006 1.2.2006
TROSKOVI POSLOVANJA 40000 72144 6548
TROSKOVI PR & MARKETING I PRODAJE 40000 57828 5689
450 Troskovi oglasavanja 40000 5689 859
451 Troskovi PR-a 40000
452 Troskovi istrazivanja trzista 40000 568
453 Troskovi donacija i sponzorstva 40000 458
454 Troskovi tiska plakata, izrade oglasa 40000 5689
455 Troskovi izrade forspana 40000 56234
456 Fotografiranje,animac.,jinglovi,logotip 40000
457 Institucij.mat.(vizitke,memo,posjet,vre) 40000
Ostali troskovi prodaje i marketinga 40000 0 0
4590 Ostali troskovi prodaje i marketinga 40000 568
4591 Troskovi nagradnih igara-prodaja 40000
4592 Troskovi spikiranja forspana-Yammat 40000

450 is exported txt cell,blanks in first columns are formulas cell..

 

[Roger Govier]

Hi
=SUMIF(GLAcc;">="&45000;Razdoblje1)-SUMIF(GLAcc;">="&46000;Razdoblje1)


I understand.
Perhaps then
=SUMPRODUCT(--(left(GLAcc,2)="45");--(Razdoblje1))

--
Regards

Roger Govier


Thanks,

but it doesnt work. This is an export file from MBS Navision plans and
it has predifined formulas in excel. And I can not change it in
Navision, maybe only in excel. Example:
Br. racuna GK Ime TC Sifra 1.1.2006 1.2.2006
TROSKOVI POSLOVANJA 40000 72144 6548
TROSKOVI PR & MARKETING I PRODAJE 40000 57828 5689
450 Troskovi oglasavanja 40000 5689 859
451 Troskovi PR-a 40000
452 Troskovi istrazivanja trzista 40000 568
453 Troskovi donacija i sponzorstva 40000 458
454 Troskovi tiska plakata, izrade oglasa 40000 5689
455 Troskovi izrade forspana 40000 56234
456 Fotografiranje,animac.,jinglovi,logotip 40000
457 Institucij.mat.(vizitke,memo,posjet,vre) 40000
Ostali troskovi prodaje i marketinga 40000 0 0
4590 Ostali troskovi prodaje i marketinga 40000 568
4591 Troskovi nagradnih igara-prodaja 40000
4592 Troskovi spikiranja forspana-Yammat 40000

450 is exported txt cell,blanks in first columns are formulas cell..

 

[Navision]

No,
I have sent you an e-mail with the file.
thx