Count per Date and Group

[mucah!t]

Hi all,

Is it possible to count the C's based on the criteria given in J4:L4?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
3		alfa	alfa	alfa	bravo	bravo	charlie	charlie		GROUP	FROM	TILL
4	1-Jan	DF	DF	DF	GH	GH	DN	DN		ALFA	5-Jan	11-Jan
5	2-Jan	DF	DF	DF	C	GH	DN	DN
6	3-Jan	DF	DF	DF						RESULT =	4
7	4-Jan
8	5-Jan	DN	DN	DN	GH	GH	GH	GH
9	6-Jan	DN	C	DN	DN	C	GH	GH
10	7-Jan	DN	DN	DN	DN	DN
11	8-Jan	C	DN	C	DN	DN
12	9-Jan				DN	DN	GH	GH
13	10-Jan				DN	DN	DN	C
14	11-Jan	GH	GH	C			DF	DF
15	12-Jan	GH	GH	GH	DG	DG	DF	DF
16	13-Jan				DG	DG	DF	DF
17	14-Jan				C	DF
18	15-Jan	GH	GH	GH	DF	DF	DN	DN
19	16-Jan	DN	DN	DN	DF	DF	DN	DN
20	17-Jan	DN	DN	DN			DN	DN
21	18-Jan	DN	DN	DN	DN	DN	C	DN
22	19-Jan	DN	DN	DN	DN	C
23	20-Jan	DN	DN	DN	DN	DN
24	21-Jan				DN	DN	GH	GH
25	22-Jan	DG	DG	DG			GH	GH
26	23-Jan	DG	DG	DG
Sheet1

Excel 2003Worksheet
</TR></TBODY>

 

[Haseeb Avarakkan]

Hello, Try

K4, Control+Shift+Enter, not just Enter

=SUM(IF(B3:H3=J4,IF(A4:A26>=K4,IF(A4:A26<=L4,IF(B4:H26="C",1)))))

 

[Aladin Akyurek]

Control+shift=enter, not just enter:

=SUM(IF($A$4:$A$26>=K4,IF($A$4:$A$26<=L4,IF($B$3:$H$3=J4,($B$4:$H$26="C")+0))))

 

[mucah!t]

Wow great, thanks guys,

I got still one thing though.
Is it possible to count all C's as 8 and to sum all C's followed by a number [like C2 or C4]?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
3		alfa	alfa	alfa	bravo	bravo	charlie	charlie		GROUP	FROM	TILL
4	1-Jan	DF	DF	DF	GH	GH	DN	DN		ALFA	5-Jan	11-Jan
5	2-Jan	DF	DF	DF	C	GH	DN	DN
6	3-Jan	DF	DF	DF						RESULT =	22
7	4-Jan
8	5-Jan	DN	DN	DN	GH	GH	GH	GH
9	6-Jan	DN	C2	DN	DN	C	GH	GH
10	7-Jan	DN	DN	DN	DN	DN
11	8-Jan	C4	DN	C	DN	DN
12	9-Jan				DN	DN	GH	GH
13	10-Jan				DN	DN	DN	C
14	11-Jan	GH	GH	C			DF	DF
15	12-Jan	GH	GH	GH	DG	DG	DF	DF
16	13-Jan				DG	DG	DF	DF
17	14-Jan				C	DF
18	15-Jan	GH	GH	GH	DF	DF	DN	DN
19	16-Jan	DN	DN	DN	DF	DF	DN	DN
20	17-Jan	DN	DN	DN			DN	DN
21	18-Jan	DN	DN	DN	DN	DN	C	DN
22	19-Jan	DN	DN	DN	DN	C
23	20-Jan	DN	DN	DN	DN	DN
24	21-Jan				DN	DN	GH	GH
25	22-Jan	DG	DG	DG			GH	GH
26	23-Jan	DG	DG	DG
Sheet1

Excel

 

[mucah!t]

The following formula works, but gives an error when the range also includes codes like "CD" or "CN".
Any ideas

ps. the formula takes about 20seconds to calculate

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[Aladin Akyurek]

What is wrong with the suggestions you've got?

 

[mucah!t]

Although not shown in the example, the range also includes codes like "CD" and "CA". This results in an error.

 

[Aladin Akyurek]

Although not shown in the example, the range also includes codes like "CD" and "CA". This results in an error.

Mucahit:

The question is what you want:

Counting C stand-alone or not? The formula I suggested does not include CD and CA at all.

 

[mucah!t]

I'd like C's to be counted as 8
C2's C4's etc to sum only the digits
and ignore codes like CD CA etc.

if this is impossible or too difficult

then I'd like only the C's with an digit to be summed.

 

[Aladin Akyurek]

I'd like C's to be counted as 8
C2's C4's etc to sum only the digits
and ignore codes like CD CA etc.

if this is impossible or too difficult

then I'd like only the C's with an digit to be summed.

Thus:

A stand-alone C ---> 1

Something like C2 ---> 2

Something like CD ---> 0

Right?