Solved

# SUMIFS

Posted on 2014-10-02
70 Views
=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
Question by:Seamus2626
• 4
• 2
• 2
• +2

LVL 19

Expert Comment

maybe would be helpful if you attach sample data
0

LVL 48

Accepted Solution

Rgonzo1971 earned 250 total points
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

Attached

Thanks
EE.xlsx
0

LVL 25

Assisted Solution

ProfessorJimJam earned 250 total points
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

Thanks guys!
0

LVL 25

Expert Comment

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

Looks like the example data does not have any record in which all criteria of your SUMIFS are met.
0

LVL 25

Expert Comment

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

LVL 11

Expert Comment

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

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

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…