Solved

SUMIFS

Posted on 2014-10-02
10
72 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
ID: 40356710
maybe would be helpful if you attach sample data
0
 
LVL 49

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
 
LVL 25

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

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 25

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 25

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy value from a certain cell 5 24
File not loading into PowerPivot 4 9
NEED TO UPDATE DATA IN EXCEL 18 27
Pivot help - Display only Is Not Null 7 14
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 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

19 Experts available now in Live!

Get 1:1 Help Now