Hi all,
I am trying to create a formula that will tally the following cells containing text based on the following criteria.
- Date (or range of dates)
- Shop
- Position
I've been trying to use a SUMIF/COUNTIF with an index/match but i can't seem to get it to work.
I've attached a sample of the sheet.
Any help is appreciated.
Thanks
Have you tried the SUMIFS function?
Martin
I'm summing cells containing only text, from my understanding, I thought you could only use sumifs for numbers?
COUNTIFS then?
yes but i dont think i've used it properly. That could be part of the problem.
It's not clear were you want the sum to go or the range of data that should be analysed.
Could you post an example with a manually created tally and some highlighting to show the source data?
Use COUNTIFS.
Not sure where the formula should go as there are no obvious calculations in your file.!
Hey john,
What I'm trying to say is to sum the total amount of a specific position, lets say "team lead", for a specific date range in a specific shop, for example "ASSEMBLY S".
Sorry, mrice i didn't notice your comment. Here is an idea of what i am looking for (check the 2nd sheet). Still struggling to get it to work, getting frustrated now. -.-
Maybe something like
pasted into B2 on Sheet2?
Please Login or Register to view this content.
I'm no longer at work so I won't be able to test it, i tried it on my sample sheet, looks accurate. thanks mrice.
Again not sure if this what you want but try
in B2 copy down
=SUMPRODUCT((Sheet1!$C$2:$C$14=Sheet2!$A2)*(Sheet1!$H$2:$BE$14=B$1))
Similar formulas for C , D
For E
=SUMPRODUCT((Sheet1!$C$2:$C$14=Sheet2!$A2)*(LEFT(Sheet1!$H$2:$BE$14,7)=D$1)*(MONTH(Sheet1!$H$1:$BE$1)>=5)*((MONTH(Sheet1!$H$1:$BE$1)<=8)))
I changed headings to incorporate in formulae.
See attached (following post!).
Last edited by JohnTopley; 05-26-2016 at 03:49 AM.
Sorry .... had problem trying to add attachment.
Sweet! I got it to work, turns out I had a couple "shops" with the an #N/A in them so it caused by formula not to work. Thanks again everyone, you guys are life savers!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks