How to Limit my countifs based on rows

[Ritche]

Hi Everyone, I been banging my head over 2 days trying to figure out how to forecast the amount of sensors.

The data I have has column B (Locations) But this location doubles up based on the different branch it has. So some location can have up to 10 entries. from C to AC are dates that each locations's branch needs more unit.

Since I have to send it to the Main location, how do I limit my countifs to just that location automatically?

I calculated the overall how much I need to send out per month using.

=COUNTIFS(Sheet4!$C:$AC,">4/1/2019", Sheet4!$C:$AC,"<4/30/2019") <-- this is an example of April.

How do I limit the count per location. Location are example ( 201,202,203,204, etc..) Do I need to add vlook up? Location are all over the cell and i would have to add it so I can't limit the range.

Also since I only need the month information is there a way to conver the 4/10/2019 to just say april 2019 and reads as April 2019? I had to do the date range because when i format the date to Apr 2019 it still has the actually date on it.

 

[arthurbr]

Perhaps a Pivot Table would be suitable? https://www.contextures.com/CreatePivotTable.html

 

[Ritche]

The pivot table doesn't count all the dates in different cell range.

 

[Ritche]

I tried using this =COUNTIFS(Sheet4!A:A,Sheet3!A5,Sheet4!$C:$AC,">="&B1, Sheet4!$C:$AC,"<="&B2) but it return #value

A= 201, B3= 4/1/2019, B2=4/30/2019

 

[Marcelo Branco]

Maybe this...

=COUNTIFS(INDEX(Sheet4!C:AC,MATCH(Sheet3!A5,Sheet4!A:A,0),0),">="&B1,INDEX(Sheet4!C:AC,MATCH(Sheet3!A5,Sheet4!A:A,0),0),"<="&B2)

M.

 

[Ritche]

Hi Marcelo, It did not count all the 201 that has April date in it. There is 16 entry of 201 and their is around 18 total April entry within all of the 201

 

[Marcelo Branco]

Hi Marcelo, It did not count all the 201 that has April date in it. There is 16 entry of 201 and their is around 18 total April entry within all of the 201

Ah, now i fully understand what you need

Assumed data in rows 1 to 100 (adjust to suit)

See if this works
=SUMPRODUCT((Sheet4!A1:A100=Sheet3!A5)*(Sheet4!C1:AC100>=B1)*(Sheet4!C1:C100<=B2))

M.

 

[Marcelo Branco]

oops... typo

Try
=SUMPRODUCT((Sheet4!A1:A100=Sheet3!A5)*(Sheet4!C1:AC100>=B1)*(Sheet4!C1:AC100<=B2))

M.

 

[Ritche]

Hi Marcelo,

That didn't work. Maybe I can email you the file. But i was wondering if the formula (Sheet4!C1:AC100>=B1)*(Sheet4!C1:AC100<=B2)) finds any entry in April.

A= 201, B1= 4/1/2019, B2=4/30/2019

 

[Marcelo Branco]

Worked perfectly for me

Sheet1

	A​	B​	C​	D​
1​		04/01/2019​		Result​
2​		04/30/2019​		13​
3​

<tbody>
</tbody>

Formula in D2
=SUMPRODUCT((Sheet4!A1:A100=Sheet3!A5)*(Sheet4!C1:AC100>=B1)*(Sheet4!C1:AC100<=B2))

Sheet4

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

2​

3​

201​

12/29/2018​

12/30/2018​

12/31/2018​

04/01/2019​

04/02/2019​

04/03/2019​

04/04/2019​

04/05/2019​

04/06/2019​

4​

5​

6​

201​

12/29/2018​

12/30/2018​

12/31/2018​

04/01/2019​

04/02/2019​

04/03/2019​

04/04/2019​

04/05/2019​

7​

8​

9​

10​

11​

12​

13​

14​

201​

12/31/2018​

04/01/2019​

04/02/2019​

15​

<tbody>
</tbody>

Sheet3!A5 = 201

M.