Solved

SUMIFS

Posted on 2014-10-02
10
70 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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 19

Expert Comment

by:helpfinder
Comment Utility
maybe would be helpful if you attach sample data
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
Comment Utility
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
Comment Utility
Attached

Thanks
EE.xlsx
0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
Comment Utility
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
Comment Utility
Thanks guys!
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
Looks like the example data does not have any record in which all criteria of your SUMIFS are met.
0
 
LVL 25

Expert Comment

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

Expert Comment

by:jkpieterse
Comment Utility
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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now