SUMIFS help if there is a + sign

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
488
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello, everyone,
I ask for your help to correct the formula, because I have a condition that is a little different and that's where my problem comes from.
I will attach a photo to see the columns I work with and respectively the end result, which gives me an error in adding the numbers when they are +
If we take BISO5 - the result should be = 360, but because in some places I have two words and two numbers, respectively, the end result is = 305, which is not true.
The formula I made is calculated, but when it is missing (or it is independent).
Please for some assistance how in the same table, to calculate correctly, if possible, of course!
Thank you in advance!
2021-02-18_142543.jpg
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I don't see any issue with your formula, but what is the format of the information in column P? Are they numbers? Why does 10+15 appear rather than 25? I wonder if Excel is treating those cells as text?
 
Upvote 0
Hello, in column P, there are two numbers, because in column J, in many cases I have even more than two words, as shown in the example. Basically, I have Marcos adding these two or more numbers, and yes then I have no problem with the formula, but I found that it gives me the wrong result, as I described in my first comment. I in the case I have, and it does not change, I try to collect only the specific word. When I count them everything is fine, but when adding they count, as you say if it is 25, but for these two words I have different points 10 for Biso5 and 15 for Rus10 in this example. And i need to get this 10 points, but i dont no how.
 
Upvote 0
P.S. - As for whether or not he thinks of Text, he shows me - General
2021-02-18_193809.jpg
 
Upvote 0
That check for "General" doesn't really tell you how the cell is treated. In some empty cell, enter the following formula... =TYPE(cell ref) and tell me what you get. Do this for a few cells: some that definitely have a number in them (like those containing only 10)...and some cells containing 10+15. You will probably find that those containing numeric values will return a "1" while those containing 10+15 will return a 2, which means they are being treated as text.

How many categories do you have, for example, MOXI7, LEV7, AMLF10, etc.? Is a fixed value assigned to each of these? If so, then there might be a more effective way to get the sums you need--in numeric format--in column P. If the list of categories is manageable, you could either incorporate them in a formula, or construct a lookup table. Let me know, and I will try to offer a better suggestion.
 
Upvote 0
Hi, if I did everything right, it shows me 16.
In principle, for each word I have made in the same workbook, a worksheet in which for each word there are specific points. But, but, in this whole database, if I see any word in a range of 6 months, it turns out that at one point - for example, this BISO5, it can be 10, and it can be 0 points. And everything that has been done so far works flawlessly and very correctly. But for the reason that the lines are in the thousands and I have to make a filter - countless many times, I decided to make a base in which for each word, whether there are any points or is 0 (I calculated it before) to give me the finished result.
I remain available if I have to cooperate
My error - for 10 - is 1, for 10+15 = 2
2021-02-18_195333.jpg
 
Upvote 0
Ideally, you should post a sample of your worksheet with the XL2BB add-in so that this issue can be more easily investigated. When I typed the words "cell ref" you are to insert a reference to the cell whose TYPE you would like to know...not type the literal text string "cell ref". For example, it appears that six rows above your red circles is an entry in column P that shows "10+15". What row is that? I can't tell because you haven't shown enough information. For the sake of illustration, suppose it occurs on row 10. Then in cell Q10, enter =TYPE(P10)

Sorry...I just noticed your follow up at the end of what you typed. You got 1's and 2's. The 2's mean those cells are being treated as text, not numbers...so they will not be included when you attempt to sum them.

What specifically are you trying to accomplish? You have a macro that apparently inserts numbers or text in column P depending on what you type in column J...is that correct? And then you want to extract a sum from your data table based on a date range, a name, and a category (such as all entries containing BISO5)? Is that correct?
 
Last edited:
Upvote 0
Here is link with file (be sure, no viruses)
test bro.xlsm

You have a macro that apparently inserts numbers or text in column P depending on what you type in column J...is that correct?
Is correct
And then you want to extract a sum from your data table based on a date range, a name, and a category (such as all entries containing BISO5)? Is that correct?
1000% correct

If i type example ( Lev7+Biso5+RUS10+Doxa2, will be something like this in P - 2+10+15+5
or if a BISO5+AMLF10 will be 10+15
There can be any combinations that will be supplemented with new words over time, so I'm looking for this way to make it easier for me in the future! :)
 
Last edited:
Upvote 0
Here is one idea that eliminates the need for a macro. Instead, build a helper table somewhere that can be easily modified, shown here in columns AU:AV. The "Point" column uses this table to find the point values associated with the categories that are typed into column K. Then a SUMPRODUCT formula is used to determine which rows have the relevant information in them based on your filter criteria (date range, name, and category).
MrExcel20210217 (version 2).xlsb
BDJPANAOAPAQARAUAV
1nameDatekkkpoints1/4/20211/8/2021namesumproHelper Lookup Table
2Dave Toni1/2/2021BISO510Dave Toni0MOXI7CategoryValue
3Dave Toni1/3/2021MOXI71Dave Toni3LEV7AMLF51
4Dave Toni1/4/2021BISO510Dave Toni25AMLF10AMLF1015
5Dave Toni1/5/2021BISO5+AMLF1025Dave Toni3AMLF5BISO510
6Dave Toni1/6/2021LEV7+AMLF53Dave Toni55BISO5CANDF161
7Dave Toni1/7/2021BISO510COXI2001
8Dave Toni1/8/2021BISO510DOXA25
9Dave Toni1/9/2021AMLF1015IND1.5SR1
10Dave Toni1/10/2021BISO510IRB3001
11LERC101
12LEV72
13LOSF50/12.51
14MOXI71
15RUS1015
16VALCO160/12.51
17VALF1601
K0st4din
Cell Formulas
RangeFormula
AQ2:AQ6AQ2=SUMPRODUCT(($P$2:$P$20)*($D$2:$D$20>=$AN$1)*($D$2:$D$20<=$AO$1)*($B$2:$B$20=$AP2)*ISNUMBER(SEARCH($AR2,$J$2:$J$20))*($AR2<>""))
P2:P10P2=SUMPRODUCT(--(ISNUMBER(SEARCH($AU$3:$AU$17,J2))),$AV$3:$AV$17)
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top