Solved

SUMIFS

Posted on 2014-10-02
10
75 Views
Last Modified: 2014-10-02
=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
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 40356710
maybe would be helpful if you attach sample data
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 40356713
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
 

Author Comment

by:Seamus2626
ID: 40356720
Attached

Thanks
EE.xlsx
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 40356725
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
 

Author Closing Comment

by:Seamus2626
ID: 40356730
Thanks guys!
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40356736
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40356738
Looks like the example data does not have any record in which all criteria of your SUMIFS are met.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40356742
=SUMIFS(Data!R:R,Data!I:I,"ASP",Data!BH:BH,{"80-90%";"90-100%"})
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40356744
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
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40356745
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question