Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-20-2020, 07:57 PM
FrancisSIP FrancisSIP is offline COUNTIF to count long text/values in cells (#VALUE! Error) Windows 8 COUNTIF to count long text/values in cells (#VALUE! Error) Office 2013
Novice
COUNTIF to count long text/values in cells (#VALUE! Error)
 
Join Date: Apr 2019
Posts: 5
FrancisSIP is on a distinguished road
Default 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:
  • Same Cost Code (Column A)
  • Same Description (Column C)

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.
Attached Files
File Type: xlsx COST CODE CONVERTER.xlsx (73.8 KB, 9 views)
Reply With Quote
  #2  
Old 10-20-2020, 10:39 PM
Purfleet Purfleet is offline COUNTIF to count long text/values in cells (#VALUE! Error) Windows 10 COUNTIF to count long text/values in cells (#VALUE! Error) Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsx COST CODE CONVERTER_Purfleet.xlsx (102.4 KB, 6 views)
Reply With Quote
  #3  
Old 10-21-2020, 04:56 AM
ArviLaanemets ArviLaanemets is offline COUNTIF to count long text/values in cells (#VALUE! Error) Windows 8 COUNTIF to count long text/values in cells (#VALUE! Error) Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Attached Files
File Type: xlsx COST CODE CONVERTER.xlsx (87.0 KB, 8 views)
Reply With Quote
  #4  
Old 10-25-2020, 09:22 AM
p45cal's Avatar
p45cal p45cal is offline COUNTIF to count long text/values in cells (#VALUE! Error) Windows 10 COUNTIF to count long text/values in cells (#VALUE! Error) Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsx msofficeforums45838CostCodeConvertor.xlsx (41.0 KB, 5 views)
Reply With Quote
  #5  
Old 10-25-2020, 06:33 PM
FrancisSIP FrancisSIP is offline COUNTIF to count long text/values in cells (#VALUE! Error) Windows 8 COUNTIF to count long text/values in cells (#VALUE! Error) Office 2013
Novice
COUNTIF to count long text/values in cells (#VALUE! Error)
 
Join Date: Apr 2019
Posts: 5
FrancisSIP is on a distinguished road
Default

Thank you all!

These are immensely helpful and currently looking at these solutions to see which best will serve us in the long run.
Reply With Quote
  #6  
Old 10-29-2020, 04:00 AM
amritabansal amritabansal is offline COUNTIF to count long text/values in cells (#VALUE! Error) Windows 7 64bit COUNTIF to count long text/values in cells (#VALUE! Error) Office 2007
Novice
 
Join Date: Oct 2020
Location: Gurgaon
Posts: 2
amritabansal is on a distinguished road
Default

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)
Attached Files
File Type: xlsx ABC_Master.xlsx (14.0 KB, 7 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help on Countif Error vikaskhengre Excel 1 04-09-2019 04:51 AM
COUNTIF to count long text/values in cells (#VALUE! Error) 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
COUNTIF to count long text/values in cells (#VALUE! Error) How to count the total cells that contains text data? Learner7 Excel 1 06-30-2011 04:13 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft