Sumproduct / Countif - date range/text

[Arafruafrog]

Hi Forum

I have a list of dates in A1 (eg: 1 - 30 April), Names in A2 (not counting these) and a status (Seen or Not Seen...) in A3.

I want to count the number where status = Seen within a date period - eg: 1-10 April.

I am using Excel 2003 and 2010 so the formula must work in all versions of Excel.

I have tried various versions of these formulas without success...

=COUNT(IF(Apr!$A$2:$A$10<="01/04/2017",IF(Apr!$A$2:$A$10>="04/04/2017",IF(Apr!$F$2:$F$10,"Seen",1))))

=Sumproduct(Apr!$A$2:$A$10 datevalue<= "2017,4,1")*(Apr!$A$2:$A$10,Datevalue<="2017,4,10")*(Apr!$F$2:$F$10,"Not Seen*")

Help would be great thanks Chris

 

[Marcílio_Lobão]

Chris, Good evening.

Try to use:

=SUMPRODUCT((A2:A10>=DATE(2017,4,1))*(A2:A10<=DATE(2017,4,10))*(F2:F10="Not Seen"))

Please tell us if it worked as desired.

I hope it helps.

Marcílio Lobão

 

[Arafruafrog]

Hi Marcilio

It will work for the Seen status but not for the Not Seen status.

I am using =SUMPRODUCT((A2:A10>=DATE(2017,4,1))*(A2:A10<=DATE(2017,4,10))*(D2:D10 ="Not Seen*"))

I am using the * as there are 3 Not seen status eg: Not seen - did not wait, Not seen - triaged, Not seen - no room

[TABLE="width: 420"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]APRIL[/TD]
[TD]No[/TD]
[TD]NAME[/TD]
[TD]Status[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/04/2017[/TD]
[TD]1[/TD]
[TD]P1[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/04/2017[/TD]
[TD]2[/TD]
[TD]P2[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/04/2017[/TD]
[TD]3[/TD]
[TD]P3[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/04/2017[/TD]
[TD]4[/TD]
[TD]P4[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/04/2017[/TD]
[TD]5[/TD]
[TD]P5[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/04/2017[/TD]
[TD]6[/TD]
[TD]P6[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/04/2017[/TD]
[TD]7[/TD]
[TD]P7[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/04/2017[/TD]
[TD]8[/TD]
[TD]P8[/TD]
[TD]Seen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/04/2017[/TD]
[TD]9[/TD]
[TD]P9[/TD]
[TD]Not seen - Triaged[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks Chris

 

[Aladin Akyurek]

Counting "seen" records, using SumProduct, just enter...

=SUMPRODUCT(--($A$2:$A$10>=DATE(2017,4,1)),--($A$2:$A$10<=DATE(2017,4,10),--($F$2:$F$10="seen"))

Counting "seen" records, using COUNT, control+shift+enter...

=COUNT(IF($A$2:$A$10>=DATE(2017,4,1),IF($A$2:$A$10<=DATE(2017,4,10),IF($F$2:$F$10="seen",1))))

Counting "not seen" records, using SUMPRODUCT, just enter...

=SUMPRODUCT(--($A$2:$A$10>=DATE(2017,4,1)),--($A$2:$A$10<=DATE(2017,4,10),--ISNUMBER(SEARCH("not seen",$F$2:$F$10)))

Counting "not seen" records, using COUNT, control+shift+enter...

=COUNT(IF($A$2:$A$10>=DATE(2017,4,1),IF($A$2:$A$10<=DATE(2017,4,10),IF(ISNUMBER(SEARCH("not seen",$F$2:$F$10)),1))))

 

[Arafruafrog]

Hi Aladin

Worked for a charm many thanks - just query why would I use the -- ?

Thanks Chris

 

[Aladin Akyurek]

Hi Aladin

Worked for a charm many thanks - just query why would I use the -- ?

Thanks Chris

The syntax of SumProduct is:

SUMPRODUCT(Array1, Array2, Array3, ...)

Array ==

A2:A10

B2:B10 = "jon"

C2:C10 = 10

LEFT(Q2:Z2,3) = "ada"

F2:F10 > DATE(2014,3,1)

Etc.

1. Arrays with relational operators like =, >, etc. consist necessarily of TRUE and FALSE evaluations.

2. TRUE = 1 and FALSE = 0 in Excel.

3. Any operation that can coerce a TRUE to 1 or a FALSE to 0 is called a coercer.

4. Some known coercers are: TRUE+0, --FALSE, etc.

5. SUMPRODUCT requires numbers.

6. A formula expression like (let A2:A10 be numeric)...

SUMPRODUCT(A2:A10,B2:B10="Abraham",F2:F10 > 10)

does not satisfy [5], but

SUMPRODUCT(A2:A10,--(B2:B10="Abraham"),--(F2:F10 > 10))

will, because TRUE and FALSE values in the second and the third arrays will all become 1 and 0 values.

Hence, the -- usage.

Hope this helps.

 

[Arafruafrog]

Excellent many thanks.

 

[Aladin Akyurek]

Excellent many thanks.

You are welcome.