SOLVED

Trying to count text using a date as reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2805789%22%20slang%3D%22en-US%22%3ETrying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805789%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20help%20to%20count%20the%20number%20of%20items%20based%20on%20a%20date.%3C%2FP%3E%3CP%3Emy%20dates%2001-Dec%20through%20to%2025-dec%20are%20in%20column%20c.%20the%20items%20i%20need%20to%20count%20are%20in%20columns%20d%20through%20to%20ac%20there%20are%209%20possible%20types%20of%20items%3C%2FP%3E%3CP%3Ei'm%20trying%20to%20use%20a%20formula%20to%20know%20how%20many%20specific%20items%20i%20have%20on%20a%20specific%20date.%20%26nbsp%3B%3C%2FP%3E%3CP%3Eie.%20on%20the%2001%20December%20how%20many%201's%2C%202's%203's%20items%20ect%20there%20are%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei'm%20trying%20countifs%20but%20can't%20make%20the%20formula%20work%20across%20the%20multiple%20columns%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2805789%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805829%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173040%22%20target%3D%22_blank%22%3E%40DaveK25483%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPivotTable%20could%20work%2C%20do%20you%20consider%20such%20option%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805850%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805850%22%20slang%3D%22en-US%22%3Ethanks%20for%20the%20reply%2C%20i%20was%20looking%20to%20making%20a%20new%20sheet%20with%20the%20date%20specific%20to%20the%20date%20and%20item%20numbers%20hence%20the%20formula%20idea%20but%20i%20will%20certainly%20look%20at%20the%20pivot%20table%20idea%20thanks%20good%20idea..%20However%20It's%20annoying%20that%20my%20formula%20only%20produces%20a%20zero%20a%20like%20to%20know%20where%20i'm%20going%20wrong%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805869%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173040%22%20target%3D%22_blank%22%3E%40DaveK25483%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20provide%20small%20sample%20file%20and%20mention%20on%20which%20Excel%20version%20%2F%20platform%20you%20are%2C%20we%20may%20try%20to%20play%20with%20formulas%20for%20your%20case.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805975%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805975%22%20slang%3D%22en-US%22%3Eforgot%20to%20add%20using%202011%20on%20a%20mac%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805969%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated%3C%2FP%3E%3CP%3EFile%20attached%2C%20i'm%20trying%20to%20build%20the%20table%20on%20the%20Orders%20Tab%2C%20from%20the%20test%20date%20on%20the%20form%20response%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2811992%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2811992%22%20slang%3D%22en-US%22%3EHi%20received%20this%20mail..below%20but%20can't%20trace%20where%20the%20results%20are%2C%20sorry%20for%20being%20dense%3CBR%20%2F%3E..mail%20begin..%3CBR%20%2F%3EHi%3CBR%20%2F%3EMicrosoft%20Tech%20Community%20%3CMICROSOFTTECHCOMMUNITY%3E%3CBR%20%2F%3EMon%2004%2F10%2F2021%2006%3A34%3CBR%20%2F%3EHere's%20the%20main%20Pivot%20View%3A%3CBR%20%2F%3Ewith%20the%20formula%20count%20below%20it%2C%20and%20other%20pivot%20views%20above%20and%20below%20it.%3A%3CBR%20%2F%3Echeers%3CBR%20%2F%3Email%20end....%3C%2FMICROSOFTTECHCOMMUNITY%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2812000%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20count%20text%20using%20a%20date%20as%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2812000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173040%22%20target%3D%22_blank%22%3E%40DaveK25483%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20removed%20it%20because%20my%20solution%20is%20not%20based%20on%20mac%20but%20here%20take%20a%20look%20see%20if%20you%20can%20work%20it%20out%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Need help to count the number of items based on a date.

my dates 01-Dec through to 25-dec are in column c. the items i need to count are in columns d through to ac there are 9 possible types of items

i'm trying to use a formula to know how many specific items i have on a specific date.  

ie. on the 01 December how many 1's, 2's 3's items ect there are 

 

i'm trying countifs but can't make the formula work across the multiple columns

12 Replies

@DaveK25483 

PivotTable could work, do you consider such option?

thanks for the reply, i was looking to making a new sheet with the date specific to the date and item numbers hence the formula idea but i will certainly look at the pivot table idea thanks good idea.. However It's annoying that my formula only produces a zero a like to know where i'm going wrong

@DaveK25483 

If you provide small sample file and mention on which Excel version / platform you are, we may try to play with formulas for your case.

@Sergei Baklan 

Hi Sergei

 

Much appreciated

File attached, i'm trying to build the table on the Orders Tab, from the test date on the form response tab.

 

forgot to add using 2011 on a mac
Hi received this mail..below but can't trace where the results are, sorry for being dense
..mail begin..
Hi
Microsoft Tech Community <MicrosoftTechCommunity@us.khoros-mail.com>
Mon 04/10/2021 06:34
Here's the main Pivot View:
with the formula count below it, and other pivot views above and below it.:
cheers
mail end....

@DaveK25483 

 

I removed it because my solution is not based on mac but here take a look see if you can work it out

Thankyou for you help the pivot looks good but a little too detailed for use.
So i'd really like to return to the count idea if possible. Filling in the order tab.. countifs should work but just can't get it too, i've also tried sum product, and vlookup won't work because there could be more than one date formula.
I'm at a total loss.

@DaveK25483 

I modified it full Pivot View with slicers if its counts you need counts you will get:

Yea_So_0-1633426189447.png

Sliced View:

Yea_So_1-1633426293069.png

Sliced View with two party times:

Yea_So_2-1633426374221.png

 

 

 

 

 

 

best response confirmed by DaveK25483 (Occasional Contributor)
Solution
Brilliant
Only problem my mac doesn't support slicers but really thank you much appreciated

rgds

@DaveK25483 

 

That's your business we're talking about, If you want to drive a ferrari in the numbers farm suit yourself or you can buy a Windows Pick-up truck for your farm. Its your business.

 

cheers

like it...