Is it possible... for a SUMIFS or COUNTIFS

[Mikec1012]

Need to add data for 12 employees over 7 categories and filter that with a cell that will allow to change the date and it adjusts the formula based off the date entered.

 

[mrshl9898]

Could you give an example of before and after what you are hoping to achieve?

 

[Mikec1012]

So I wanted to try and share a photo but it won't let me due to having to select a URL. is there another way to show what I am talking about?

 

[mrshl9898]

There are Table Options in the advanced reply section.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mikec1012]

[TABLE="width: 250"]
<tbody>[TR]
[TD]

[/TD]
[TD][/TD]
[TD]10/12/17
[/TD]
[TD]10/13/17
[/TD]
[TD]10/14/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michael
[/TD]
[TD]CLEX01
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]ENTER DATE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX01 PFM
[/TD]
[TD]55
[/TD]
[TD]40
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Michael
[/TD]
[TD]CLEX01
[/TD]
[TD]XX
[/TD]
[TD]CLEX01 PFM
[/TD]
[TD]XX
[/TD]
[TD]CLEX02 PFM
[/TD]
[TD]XX
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX02
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John
[/TD]
[TD]CLEX01
[/TD]
[TD]XX
[/TD]
[TD]CLEX01 PFM
[/TD]
[TD]XX
[/TD]
[TD]CLEX02 PFM
[/TD]
[TD]XX
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX02 PFM
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Susan
[/TD]
[TD]CLEX01<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD]CLEX01 PFM<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD]CLEX02 PFM<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX03
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jim
[/TD]
[TD]CLEX01<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD]CLEX01 PFM<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD]CLEX02 PFM<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX04
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Steve
[/TD]
[TD]CLEX01<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD]CLEX01 PFM<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD]CLEX02 PFM<strike></strike>
[/TD]
[TD]XX<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEXTX01
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]CLEX01
[/TD]
[TD]85
[/TD]
[TD]29
[/TD]
[TD]55
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So with the above, I would enter 10/12/17 and it would pull the data for the XX based off the name in the data already summed on the left. Hope this clarifies it better

 

[Mikec1012]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]10/12/17[/TD]
[TD]10/13/17[/TD]
[TD]10/14/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michael[/TD]
[TD]CLEX01[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD]ENTERDATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX01 PFM[/TD]
[TD]15[/TD]
[TD]23[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]Michael[/TD]
[TD]CLEX01[/TD]
[TD]XX[/TD]
[TD]CLEX01PFM[/TD]
[TD]XX[/TD]
[TD]CLEX02[/TD]
[TD]XX[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX02[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]CLEX01[/TD]
[TD]XX[/TD]
[TD]CLEX01PFM[/TD]
[TD]XX[/TD]
[TD]CLEX02[/TD]
[TD]XX[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEXTX01[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEXTX02[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]CLEX01[/TD]
[TD]17[/TD]
[TD]45[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX01 PFM[/TD]
[TD]29[/TD]
[TD]17[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX02[/TD]
[TD]11[/TD]
[TD]14[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SO with the above example, I would like to enter a date to search the data on the left, and then fill the XX with the corresponding data. End result would be ---> Michael CLEX01 25 CLEX01PFM 20... etc etc

Hope this helps.

 

[Mikec1012]

Hello there. So the example finally posted. If you have any ideas, I'd appreciate the tips or solution.

 

[t0azt]

Do you always have the same number of rows between names?

 

[Mikec1012]

Yes. The only thing I will be changing is when a new month rolls around are the columns.

Hopefully this will paint a better picture of the data. The DATE1 will be the day of the month.

There are I typed that up quick last night for an idea. Here is what it will contain:

Employee is cell A1 for reference
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD][/TD]
[TD]DATE1[/TD]
[TD]DATE2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michael[/TD]
[TD]CLEX01[/TD]
[TD]55[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]{change date here}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MICHAEL[/TD]
[TD]SUSAN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX01[/TD]
[TD]0[/TD]
[TD]19[/TD]
[TD][/TD]
[TD]CLEX01[/TD]
[TD]SUM based off date[/TD]
[TD][/TD]
[TD][/TD]
[TD]SUM FOR ALL WORK ON DATE {CHANGE DATE HERE CELL}[/TD]
[TD]SUM FOR ALL WORK ON DATE {CHANGE DATE HERE CELL}[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX03[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]CLEX02[/TD]
[TD]SUM based off date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX04[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]CLEX03[/TD]
[TD]SUM based off date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX05[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]CLEX04[/TD]
[TD]SUM based off date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX06[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]CLEX05[/TD]
[TD]SUM based off date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX07[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]CLEX06[/TD]
[TD]SUM based off date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]CLEX01[/TD]
[TD]25[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]CLEX07[/TD]
[TD]SUM based off date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEX02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Seems this may work best as a Pivot table.

If you repeat down the employee name, name in rows and CLE*** in columns and drag the date in question into values.

Otherwise.... I pasted your first example into Excel.

Michael in A3, ENTERDATE in H3.


=VLOOKUP(I4,INDIRECT(ADDRESS(MATCH($H4,$A:$A,0),2)&":"&ADDRESS(MATCH($H4,$A:$A,0)+4,5)),MATCH($H$3,$B$2:$E$2,0),0)

You basically use MATCH to locate where things are on the sheet, then ADDRESS turns them into a reference like $A$1, then INDIRECT turns that into a useable address.

The +4 in the 2nd MATCH is assuming you have 5 CLE items per name, if this is not the case this can be extended but only if the CLE names are always the same per person. Otherwise you'll need another solution.