#1
|
|||
|
|||
COUNTIF to count long text/values in cells (#VALUE! Error)
Hi there,
I have this Excel sheet that groups multiple same items from the first column into one on the last column. The grouping variables are:
Will group the items together as 1 item. The issue I'm having in the grouping process is that the Unique Quantity value (column J) comes out as a #VALUE! error due to Description (Column C) being excessively long. But is fine in some cases like in H50! We would like to retain the values in Description for quoting purposes. I've tried changing the format of the cells from General to Text but does nothing. Is there a work-around solution to this? Maybe have Unique Quantity pick a different set of variables? I don't know. Thanks heaps in advance! See the Excel sheet attached, and have highlighted the problematic bits in question. |
#2
|
|||
|
|||
I think the issue is the 255 character limit - the 4 rows that errored are all over 255 character when you combine the 2 text fields
i have tried it with a SUMPRODUCT to replace the sumifs and it appears to show the same results as your except the errors are gone (only limited to the first 200 rows at the moment). =IF(A2="","",IF(SUMPRODUCT(--($L$1:L2=L2))=M2,SUMPRODUCT(--(L2=$L$2:$L$200)*(D2=$D$2:$D$200)*($B$2:$B$200))," ")) I didnt know what the first columns actually were so i just named them with generic titles (was playing with pivot tables at first) and i have added in a number of columns to count and check |
#3
|
|||
|
|||
I'm not sure does this help you or not, but attached is an example how to calculate unique identifiers (column CODE) based on 2 columns of your data.
You also can drop 1st pair of comparision in formula for Col3ValNo. Then you get end part of CODE always same for same values in Column3. |
#4
|
||||
|
||||
Power Query seems to handle long strings well.
If I grouped only by Cost Code and Description you'd have one entry for 03.01 SIP Wall Factory Labour || Sipwall Fac. Labour - Load In / Out where there are two different Unit Costs; what do you want to place in the Unit Cost column? The same applies to: 9000 GST || . where you have 3 different Unit Costs. In the attached the grouping is done by Cost Code, Description & Unit Cost and I've highlighted the rows concerned. In the attached there's a table at cell M1 which is the query table (refresh by right-clicking and choose Refresh), your current results as plain values at cell G1 and your original data table at cell A1 minus its final calculated column. Power Query can probably handle what you want, but it's knowing what you want. |
#5
|
|||
|
|||
Thank you all!
These are immensely helpful and currently looking at these solutions to see which best will serve us in the long run. |
#6
|
|||
|
|||
Countif
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. =Countif(Range, Criteria) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help on Countif Error | vikaskhengre | Excel | 1 | 04-09-2019 04:51 AM |
count the number of text in a cell based off a different cells text | Kubi | Excel | 4 | 08-24-2017 05:53 PM |
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug) | slaycock | Word VBA | 0 | 02-18-2017 07:00 AM |
Header must toggle text & color + show count of conditionally formatted cells below | Franktoon | Excel | 3 | 02-18-2014 02:10 PM |
How to count the total cells that contains text data? | Learner7 | Excel | 1 | 06-30-2011 04:13 PM |