SUMIFS

=SUMIFS(Data!R:R,Data!I:I,Data!I3,Data!BH:BH,"80-90%",Data!BH:BH,"90-100%")

That is returning blank when it should be returning a number, is it syntaxed correctly?

Many thanks
Seamus2626Asked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

I suppose you want

=SUMIFS(Data!R:R,Data!I:I,Data!I3,Data!BH:BH,"80-90%")+SUMIFS(Data!R:R,Data!I:I,Data!I3,Data!BH:BH,"90-100%")

in your original formula you have if BH:BH = "80-90%" and BH:BH = "90-100%" which is not posiible to have to different values

Regards
0
 
helpfinderIT ConsultantCommented:
maybe would be helpful if you attach sample data
0
 
Seamus2626Author Commented:
Attached

Thanks
EE.xlsx
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ProfessorJimJamConnect With a Mentor Commented:
replace this =SUMPRODUCT(--(Data!I:I="ASP"),--(Data!BH:BH="90-100%"),--(Data!BH:BH="80-90%"),Data!R:R)  with this

=SUMPRODUCT(--(Data!I:I="ASP"),--(Data!BH:BH="90-100%")+(Data!BH:BH="80-90%"),Data!R:R)

becuase you have OR condition here on the Data!BH:BH   you get zero.   i put a plus which in the array formula means OR function and it worked.
0
 
Seamus2626Author Commented:
Thanks guys!
0
 
ProfessorJimJamCommented:
and if you want to do it in SUMIFS way then replace it with

=SUMIFS(Data!R:R,Data!I:I,"EU",Data!BH:BH,"80-90%",Data!BH:BH,"90-100%")
0
 
jkpieterseCommented:
Looks like the example data does not have any record in which all criteria of your SUMIFS are met.
0
 
ProfessorJimJamCommented:
=SUMIFS(Data!R:R,Data!I:I,"ASP",Data!BH:BH,{"80-90%";"90-100%"})
0
 
jkpieterseCommented:
Moreover, it is impossible for column BH to match both 80-90% and 90-100% on any given row so your formula will always return zero.
0
 
ProfessorJimJamCommented:
easy way with SUMIFs

=SUMIFS(Data!R:R,Data!I:I,"ASP",Data!BH:BH,{"80-90%";"90-100%"})

with sumproduct =SUMPRODUCT(--(Data!I:I="ASP"),--(Data!BH:BH="90-100%")+(Data!BH:BH="80-90%"),Data!R:R)
0
All Courses

From novice to tech pro — start learning today.