SUMPRODUCT formula revision

[Lil Stinker]

Hello,
I am using the following SUMPRODUCT formula to count the number of dates that fall within a 14 day period.
=SUMPRODUCT((H2:H10000>=TODAY())*(H2:H10000<=TODAY()+14))

How do I alter this to count only the dates based on certain criteria in column C? For instance, in the data set example below, how do I count only the return dates where "*CP200*" shows or just the return dates where "*Charger*" is listed?

Book1.xlsm
	A	B	C	D	E	F	G	H
1	Item ID	SerialNumber	Model	Description	Status	Location	DateOut	ReturnDate
2	47	6878	Motorola Radius CP200	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
3	64	50654	Motorola Radius CP200	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
4	65	7894	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
5	66	8183	Motorola Charger	UHF 16 Channel Walkie	IN	Warehouse
6	67	9504	Motorola Charger	UHF 16 Channel Walkie	IN	Warehouse
7	68	9545	Motorola Charger	UHF 16 Channel Walkie	IN	Warehouse
8	69	10500	Motorola Power Supply	UHF 16 Channel Walkie	IN	Warehouse
9	70	11050	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
10	71	12005	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
11	72	18456	Motorola Charger	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
12	73	25452	Motorola Power Supply	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
13	74	25453	Motorola Power Supply	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
14	75	65006	Motorola Power Supply	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
15	76	80000	Motorola Radius CP200	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
16	77	5500	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
17	78	6500	Motorola Charger	UHF 16 Channel Walkie	IN	Warehouse
18	79	94658	Motorola Charger	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
19	80	85858	Motorola Radius CP200	UHF 16 Channel Walkie	OUT	OC Carnival Event	2/3/2023	2/13/2023
Sheet2

 

[Joe4]

Should be able to do this with COUNTIFS:

=COUNTIFS(H2:H10000,">=" & TODAY(),H2:H10000,"<=" & TODAY()+14,C2:C10000,"*CP200*")

and

=COUNTIFS(H2:H10000,">=" & TODAY(),H2:H10000,"<=" & TODAY()+14,C2:C10000,"*Charger*")

 

[DanteAmor]

Just in case you want to count if it has "*CP200*" or "*Charger*"

=SUM(COUNTIFS(H:H,">="&TODAY(),H:H,"<="&TODAY()+14,C:C,{"*CP200*","*Charger*"}))

 

[awoohaw]

This is a sumproduct method:

=SUMPRODUCT(
(ISNUMBER(FIND("Charger",$C$2:$C$19))+ISNUMBER(FIND("CP200",$C$2:$C$19)))
*($H$2:$H$19<=$K$1)*($H$2:$H$19<=($K$1+14))*($H$2:$H$19<>"")
)

 

[Joe4]

This is a sumproduct method:

=SUMPRODUCT(
(ISNUMBER(FIND("Charger",$C$2:$C$19))+ISNUMBER(FIND("CP200",$C$2:$C$19)))
*($H$2:$H$19<=$K$1)*($H$2:$H$19<=($K$1+14))*($H$2:$H$19<>"")
)

Yeah, its a bit more complex using SUMPRODUCT that it is using COUNTIFS!

 

[awoohaw]

OP started the question with SUMPRODUCT in mind, and specifically asked how to alter his SUMPRODUCT formula.

 

[awoohaw]

@Lil Stinker , in the formula in post #4, I removed the TODAY() function and hard coded a date in K1 so you can get results. Using TODAY with your data gets a zero as of today.

 

[Joe4]

OP started the question with SUMPRODUCT in mind, and specifically asked how to alter his SUMPRODUCT formula.

Fair enough!

I find that many times people use a certain function because either someone else created it a long time ago and that is what they used, or they are unaware of other functions.
In older versions of Excel, there is COUNTIF but not COUNTIFS. So back then if you wanted to count multiple conditions, you had to use SUMPRODUCT.
But now that COUNTIFS exists, it is a little easier to use that instead!

In any event, they now have multiple solutions to choose from!

 

[Lil Stinker]

Should be able to do this with COUNTIFS:

=COUNTIFS(H2:H10000,">=" & TODAY(),H2:H10000,"<=" & TODAY()+14,C2:C10000,"*CP200*")

and

=COUNTIFS(H2:H10000,">=" & TODAY(),H2:H10000,"<=" & TODAY()+14,C2:C10000,"*Charger*")

@Joe4 Works like a charm! If I wanted to add farther date ranges, I could just change 14 to 30 or 60 or 90 days?

 

[Lil Stinker]

Just in case you want to count if it has "*CP200*" or "*Charger*"

=SUM(COUNTIFS(H:H,">="&TODAY(),H:H,"<="&TODAY()+14,C:C,{"*CP200*","*Charger*"}))

@DanteAmor Appreciate it, Dante, thanks again!