=_xlfn.SUMIFS

[Curly_Coy]

Hi Everyone,
First thread ever!
I'm having a problem with this function
_xlfn.SUMIFS($B$5:$B$24,$E$5:$E$24,"PE",$F$5:$F$24,"<=1")
I believe it's something to do with 2003 v 2007
The main problem is with the "PE" in a drop box, once changed from the original I get #NAME?

 

[Aladin Akyurek]

Hi Everyone,
First thread ever!
I'm having a problem with this function
_xlfn.SUMIFS($B$5:$B$24,$E$5:$E$24,"PE",$F$5:$F$24,"<=1")
I believe it's something to do with 2003 v 2007
The main problem is with the "PE" in a drop box, once changed from the original I get #NAME?

If you are on 2003, replace that with:

=SUMPRODUCT(--(E$5:$E$24="PE"),--($F$5:$F$24<=1),$B$5:$B$24)

or with:

=SUM(IF(E$5:$E$24="PE",IF($F$5:$F$24<=1,$B$5:$B$24)))

which needs to be confirmed with control+shift+enter, not just with enter.

 

[Curly_Coy]

Thank you so Aladin Akyurek, it works.
1 from 1, not lost now, cheers

 

[Aladin Akyurek]

Thank you so Aladin Akyurek, it works.
1 from 1, not lost now, cheers

You are welcome. Thanks for providing feedback.

 

[Vagabond663]

I am currently trying to run a trends tracker on excel 2003 but for some reason nothing I try seems to work.
=_xlfn.sumifs(january!$C$5:$C35,january!$D$5:$D$35,summary!$B24)
I can provide a link to the file if necessary.

 

[Aladin Akyurek]

I am currently trying to run a trends tracker on excel 2003 but for some reason nothing I try seems to work.
=_xlfn.sumifs(january!$C$5:$C35,january!$D$5:$D$35,summary!$B24)
I can provide a link to the file if necessary.

Just enter:

=SUMPRODUCT(january!$C$5:$C35,--(january!$D$5:$D$35=summary!$B24))

Or control+shift+enter, not just enter:

=SUM(IF(january!$D$5:$D$35=summary!$B24,january!$C$5:$C35))

 

[Jonmo1]

Since it's only 1 criteria, try SUMIF

=SUMIF(january!$D$5:$D$35,summary!$B24,january!$C$5:$C35)

 

[Vagabond663]

Its looking for data on other pages. I've tried what you suggested and nothing changed.
Im including a zip file that has the worksheet and a screenshot of waht i am seeing in excel 2003
https://drive.google.com/file/d/0B_pvNc_JYL_fRnREamJFQTZseU0/view?usp=drivesdk