Counting specific text from dated columns if another condition is met

alm395

New Member
Joined
Apr 23, 2018
Messages
28
I have a large amount of information that is being tracked daily. Each business unit has its own sheet that basically looks like this:

SPREADSHEET NAME: ABC
ABCD
1NameDivision10/1/202010/2/2020
2Doe, JohnCAActiveWorking From Home
3Hill, JaneSOActiveOff
4Smith, KyleSOActiveActive
5White, JakeCRSickActive
6Black, NancyCRWorking From HomeActive

I have several tables already tracking all sorts of data on this information, but one manager would like their division pulled out separately. They requested the table to look like the table below with the daily counts under the dates:

SPREADSHEET NAME: HISTORICAL
ABC
1Code10/1/202010/2/2020
2Active21
3Working From Home00
4Sick00
5Off01

The actual dates will range from 3/20/2020 - 12/31/2020.

What is the best formula to use saying if the Division = SO on Spreadsheet ABC, count the number of times "Active" appeared for 10/1/2020 (should = 2)?

Please help!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
944
Office Version
  1. 365
Platform
  1. Windows
Try this

Book1
ABCDEFGHIJ
3NameDivision10-01-202010-02-2020Division NameCA
4Doe, JohnCAActiveWorking From Home10-01-2020
5Hill, JaneCAoffOffActive1
6Smith, KyleSOActiveActiveWorking from Home0
7White, JakeCAoffActiveSick0
8Black, NancyCAoffActiveoff3
9
Sheet2
Cell Formulas
RangeFormula
J5:J8J5=SUM(IFERROR(SEARCH(H5,IF($I$3=$B$4:$B$8,INDEX($C$4:$D$8,,MATCH($J$4,$C$3:$D$3,0))))^0,0))



Make sure that a word is not present in the word like OFF is a single word but it is also present in Playoff.
I have used Search function which will ignore this. If this is a case then please ping me again as your criteria doesn't have such case
 

alm395

New Member
Joined
Apr 23, 2018
Messages
28
Try this

Book1
ABCDEFGHIJ
3NameDivision10-01-202010-02-2020Division NameCA
4Doe, JohnCAActiveWorking From Home10-01-2020
5Hill, JaneCAoffOffActive1
6Smith, KyleSOActiveActiveWorking from Home0
7White, JakeCAoffActiveSick0
8Black, NancyCAoffActiveoff3
9
Sheet2
Cell Formulas
RangeFormula
J5:J8J5=SUM(IFERROR(SEARCH(H5,IF($I$3=$B$4:$B$8,INDEX($C$4:$D$8,,MATCH($J$4,$C$3:$D$3,0))))^0,0))



Make sure that a word is not present in the word like OFF is a single word but it is also present in Playoff.
I have used Search function which will ignore this. If this is a case then please ping me again as your criteria doesn't have such case
I cannot get it to work. "0" just calculates when I sub out my information. :(
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCD
1NameDivision01/10/202002/10/2020
2Doe, JohnCAActiveWorking From Home
3Hill, JaneSOActiveOff
4Smith, KyleSOActiveActive
5White, JakeCRSickActive
6Black, NancyCRWorking From HomeActive
Sheet1


+Fluff v2.xlsm
ABC
1Code01/10/202002/10/2020
2Active21
3Working From Home00
4Sick00
5Off01
Sheet2
Cell Formulas
RangeFormula
B2:C5B2=SUMPRODUCT((Sheet1!$B$2:$B$6="SO")*(Sheet1!$C$2:$D$6=$A2)*(Sheet1!$C$1:$D$1=B$1))
 

alm395

New Member
Joined
Apr 23, 2018
Messages
28
That worked!!! Thank you so much!!! You have no idea how much time you just saved me!!! ? ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,175,545
Messages
5,898,051
Members
434,690
Latest member
Shamsuddin M

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top