Hi there,
I have a Excel sheet attached below. I would need some assistance in using the Countif function.
Sheet 1 - Lists the Engineer and the Companies they have visited on a particular date.
Sheet 2 - A consolidated view where I need to track how many sites has a engineer travelled on a particular day.
I need to formulate this using a formula but Im unsure how to proceed. Would be great if anyone could assist me on this.
Last edited by newbie1234; 02-05-2012 at 02:11 AM.
PLs check file attached .
Tell me if it doesnt work.
Quang PT
Hi newbie1234,
See the attached Pivot Table answer. Click on the Date filter above and pick you dates to see counts.
No formulas needed - just drag and drop field names into pivot areas.
One test is worth a thousand opinions.
Hi Bebo..Originally Posted by bebo021999
Thanks alot.. Your solution works
Nice to hear thatHi Bebo..
Thanks alot.. Your solution works
Anyway, Pivot is the best offer, if you have a huge data.
Hi Bebo,
I tried to use the formulas on my excel sheet, but it doesnt seem to work. Your formulas are listed on sheet 1. I tried to incorporate them in my Sheet 2 and reference the values from sheet 1. but cant seem to get my formula working.. could you assist.
I have attached my excel sheet again with the formula added on 4th Jan for John. Please do have a look. It shows 0.5 instead of 2.
Last edited by newbie1234; 01-30-2012 at 11:05 PM.
Sorry, I had paid no attention to your sheet 2Hi Bebo,
I tried to use the formulas on my excel sheet, but it doesnt seem to work. Your formulas are listed on sheet 1. I tried to incorporate them in my Sheet 2 and reference the values from sheet 1. but cant seem to get my formula working.. could you assist.
I have attached my excel sheet again with the formula added on 4th Jan for John. Please do have a look. It shows 0.5 instead of 2.
Attachment is new format.
You typed a correct formula to sheet 2, but forgot to confirm by Ctrl-Shift-Enter, because of array formula.
Yup, I got the correct formula, i googled and found out that it was a array formula before seeing yr reply.
Thank you so much for the assist. Really appreciate your kind help
I have changed the status of this Thread to Solved. Mods please close this thread.
Thank you all for the assistance rendered
Cheers and Have a Nice day :D
Hi there,
Just to let you guys know that my data has started to grow, and by using the Array Formula my Excel Sheet has started to slow down alot.
As advised that its the right way to use a Pivot Table if my data is huge.
So i have tried using the Pivot Table provided by Marvin.
I have did some amendments to suit the layout I would want my Pivot Table to be.
But i face a simple issue, based on my Pivot Table it shows the "Count of Company" in my Values field e.g. 3 for 4th Jan for Engineer John, but what i need to capture is actually the no of locations he has travelled. I have 3 entries for Company but there is a total of only 2 Locations travelled.
How do i get a workaround for this? Please advice
Hi there,
Anyone kind enough to assist?
Hi guys,
Anyone..?
Firstly define 3 named range, then try SUMPRODUCT as in attached.
1 with helper column
1 without helper column.
....
Last edited by Haseeb Avarakkan; 02-07-2012 at 11:14 PM.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks