count if between date range and cell has red font

[leeann]

Hope someone is able to assist, would like to count data that meets three criteria in a large range. If it is between a date range AND text equals a name AND any part of the cell has red text. eg,

column A between 1 Jul 13 to 31 Jul 13 AND

Column B text equals Smith AND

Column C any part of cell text is red.

thanks

 

[Smallman]

Hi Leeann


You are setting up a scenario where you will need a custom function and why would you do that? Simply set your spreadsheet up so instead of a colour you have a column. Lets be original and call it NewCriteria and now NewCriteria becomes part of your sumifs or Sumproduct criteria. In my opinion that makes your world a lot simplier.


Take care


Smallman

 

[leeann]

Hi Smallman, unfortunately I would have to add 4 columns for every one in the sheet and redo the data. If I am not able to count I will change the sheet. thanks

 

[SirJB7]

Hi, leeann!

Any chance of uploading a sample file? Including manual examples of desired output if applicable, it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[leeann]

https://www.dropbox.com/s/1lmia11u5bawlli/test.xlsx

thanks SirJB7

 

[imla]

I see that the data is organized in complex way.

and you need ony one case of red per period (day/night)

So I think only one column is enough!


Why not juste create a simple "table".

it is true that you will enter more data but the filers

by Name or month or years or Period will be a piece of cake

[pre]

H|E  Name   Date     Period  Data  Red_char
H   jon  7/10/2013    day   a b c    b
H   jon  7/10/2013    night   d      a
E   jo   7/10/2013    day    b c
..etc

[/pre]

even you can make most of this columns as list from data validation, So a mouse click will save you most of typing!

 

[SirJB7]

Hi, leannn!


Any chance you can use a special (non-used elsewhere) character before the red strings? Instead of "a b c" with b red something like this "a @b c", it could be a single character or multiple characters, the idea is to get an distinctive prefix, @, #, @#,...


You'd still continue using your red color formatting if you want to, but your goal would be achieved with a simple formula, maybe using COUNTIFS or SUMPRODUCT functions.


Is that suitable?


Regards!


PS: If you don't want to use any visible character you'd still use this method using ALT-0160, which is another type of space, usually got when downloading files from other platforms or environments.


EDITED


PS: Maybe not a so simple formula and with other functions, I'm doing a little test.

 

[SirJB7]

Hi, leeann!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/count%20if%20between%20date%20range%20and%20cell%20has%20red%20font%20-%20test%20%28for%20leeann%20at%20chandoo.org%29.xlsx


Array formula for C12:C15 & C18:C21 cells:

=SUMAPRODUCTO((SIGNO(SI.ERROR(HALLAR(CARACTER(160)&$C12;$C$3:$P$8);0)))*(SIGNO(SI.ERROR(HALLAR($C12;$C$3:$P$8);0)))*($A$3:$A$8=INDICE($B$12:$B$23;(CONTARA($B$12:$B12)-1)*6+1))*($C$1:$P$1>=D$11)*($C$1:$P$1<=FIN.MES(D$11;0))) -----> in english: =SUMPRODUCT((SIGN(IFERROR(SEARCH(CHAR(160)&$C12,$C$3:$P$8),0)))*(SIGN(IFERROR(SEARCH($C12,$C$3:$P$8),0)))*($A$3:$A$8=INDEX($B$12:$B$23,(COUNTA($B$12:$B12)-1)*6+1))*($C$1:$P$1>=D$11)*($C$1:$P$1<=EOMONTH(D$11,0)))


About your workbook structure I only changed 1st row, eliminating merged cells and duplicating date values on next column, so as to keep the yet complex formula in such level of complexity.


For testing purposes I changed 13/07/2013 by 13/08/2013 in cells I1:J1 and cell H7 from "a b" to "d b".


Just advise if any issue.


Regards!


PS: I used the ALT-0160 character version before each read letter/text, it's up to you to use this method or any other string characters combination prefix as long as you update the formula where it says "CHAR(160)" replacing it by the chosen indicator.

 

[leeann]

thank you so much, I will replace the red character with a number and change formula to suit. The data is too large in workbook to change columns. Will try when next at work

 

[leeann]

Hi, I get the following error with the formula, are you able to assist, thanks


"Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated

 

[SirJB7]

Hi, leeann!

Ooops...! We're in trouble...

Some data, please:

a) How many rows and columns has your worksheet?

b) How much does the workbook weights in Mb?

c) How many RAM does your machine has? Tried on another one, with more memory? Operative system? Excel version? PC hard configuration (CPU, RAM, disk space)?

d) Was the PC running a lot of other tasks/programs? From the task manager (Ctrl-Alt-Del), in the 4th tab, Performance, which were the key indicators of used CPU, memory, total physical, cached, available and free?

e) Any chance of uploading the file, or a sample but of the same characteristics (no. of cells, length of strings, ...)? Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!