Change range of countif when dragging down.

[Jaffabfc]

Hi,

i have a lot of data in Sheet3 columns A:CQ

Rows all the way down to 30,000+.

So columns B:CQ are all dates, so once all the raw data (in another tab) is entered and some data appears against a date the date will appear in the cell.

below each columnd title i have several outcomes, i have blank, Backup, No Pallet, Replnished and Received.

on Sheet 5 i have the following.

Column A: Date
Column B: Received
Column C: Backup
Column D: Replenished

I am wanting as the dates are added to count how many cells equal Received in the column that says that date but to drag it down would mean changing the formula everytime (around 1000), is there any quick way round it?

Thanks

 

[footoo]

Perhaps : =COUNTIF(B:B,B2) and fill down.

 

[Jaffabfc]

Perhaps : =COUNTIF(B:B,B2) and fill down.

if i do that it will count in B:B bhat is in B2, but i am wanting this to be in b2.

So in B2 i am wanting to count what is in Sheet3!B:B and equals to B1, then in B3 i am wanting to count what is in Sheet3!C:C equals to B1, B4 sheet3!D:D etc etc

 

[Jaffabfc]

if i do that it will count in B:B bhat is in B2, but i am wanting this to be in b2.

So in B2 i am wanting to count what is in Sheet3!B:B and equals to B1, then in B3 i am wanting to count what is in Sheet3!C:C equals to B1, B4 sheet3!D:D etc etc

still no luck with this..
anyone any ideas?

 

[footoo]

It's not clear to me what your sheets look like and what you want to count.

 

[Jaffabfc]

It's not clear to me what your sheets look like and what you want to count.

OK, no problem.

ive put on an example.
https://ibb.co/Qfp3CzV
so the first spreadsheet is a list of the data i have, the 2nd is what i am wanting to count in dates.

call the top one sheet1 and bottom sheet2 to make it easy.

so as the dates grow i am wanting to drag down on sheet2 to calculate the columns on sheet1 going across.

 

[footoo]

In Sheet2 :

Enter in A2 =OFFSET(Sheet1!$B$1,0,ROW()-2) and drag down

Enter in B2 =COUNTIF(OFFSET(Sheet1!B:B,0,ROW()-2),B$1) drag down and right

 

[steve the fish]

Looking at the image i think this will work provided there is just one of each date.

=COUNTIFS(INDEX(Sheet3!$B:$CQ,,MATCH($A2,Sheet3!$B$1:$CQ$1,0)),B$1)