Thread: COUNTIF to count long text/values in cells (#VALUE! Error)
View Single Post
 
Old 10-20-2020, 10:39 PM
Purfleet Purfleet is offline Windows 10 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