Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

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
Avatar of helpfinder
helpfinder
Flag of Slovakia image
maybe would be helpful if you attach sample data
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Seamus2626
Seamus2626
Flag of Ireland image

ASKER

Attached

Thanks
EE.xlsx
SOLUTION
Avatar of Professor J
Professor J
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Seamus2626
Seamus2626
Flag of Ireland image

ASKER

Thanks guys!
Avatar of Professor J
Professor J
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%")
Avatar of Jan Karel Pieterse
Jan Karel Pieterse
Flag of Netherlands image
Looks like the example data does not have any record in which all criteria of your SUMIFS are met.
Avatar of Professor J
Professor J
=SUMIFS(Data!R:R,Data!I:I,"ASP",Data!BH:BH,{"80-90%";"90-100%"})
Avatar of Jan Karel Pieterse
Jan Karel Pieterse
Flag of Netherlands image
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.
Avatar of Professor J
Professor J
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)