I have 2 cols with values in them. I also have a third column with some text fields. I am trying to create a formula that will total the differences between the two columns but I only want to do it for those rows that have a specific criteria in the third column. Can anyone help?
You can use Sumproduct() like this:
=SUMPRODUCT(--(C1:C4="x"),A1:A4-B1:B4)
Where A1:A4 and B1:B4 house the values and C1:C4 house the text (I used "x" as criteria)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Try this,
=SUMPRODUCT(--(A1:A4)*(--(C1:C4="Criteria")))
and
=SUMPRODUCT(--(B1:B4)*(--(C1:C4="Criteria")))
alter the ranges to suit and the "Criteria" to your text
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Thank you. That works. What if my search criteria is anything in columns C that begins with "ABC" or anything that begins with "ABC" and has "FG" in it (i.e. ABC*FG*)?
Try putting a helper column in D1,
=LEFT(C1,3) then modify the formulas
=SUMPRODUCT(--(A1:A4)*(--(D1:D4="ABC")))
Originally Posted by LAF
Try (using my previous references)...
=SUMPRODUCT(--ISNUMBER(SEARCH("Abc*fg",C1:C4)),A1:A4-B1:B4)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks