Register
Log In
Unanswered
Home
March 5, 2017
Offline
Why this not work
SUMPRODUCT((A$1:A$7="FIN")*(A$1:A$7="OVH")*(B$1:B$7))??
Dear Expert,
I have data as below
A B
FIN 10
OVH 20
COS 30
FIN 40
OVH 50
COS 60
FIN 70
thanks i need only SUMPRODUCT
could you rectify my formula?
See Answer
Dashboards
Power BI
Power Query
April 21, 2015
Offline
I think it doesn't work because you give two criteria on the same column. If you evaluate the formula, you'll see that it finds the first criterium (FIN) (with true and falses) and it finds the second one also. But when it combines both everything turns into zero's. When you only use one criterium in this column it works perfect. I think it's a function where you can use more criteria but in different columns.
July 16, 2010
Offline
Hi Mey,
Try this:
=SUMPRODUCT(((A$2:A$8="FIN")+(A$2:A$8="OVH"))*(B$2:B$8))
You need to change the AND operator * between your criteria to the OR operator +.
More on how SUMPRODUCT works here: https://www.myonlinetraininghub.com/excel-sumproduct-an-alternative-to-sumifs
Mynda
Answers Post
Most Users Ever Online: 57
Currently Online: Meyanui
17 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
