Posts by etaf

    Re: Sumif wildcard help! (DAte/time)


    not sure about the wild card
    but you could try
    =SUMIFS(Data!Q:Q, Data!P:P ,">=5/4/13 00:00", Data!P:P ,"<=5/4/13 23:59:59")


    OR


    =SUMPRODUCT(--(Data!P:P>DATE(2013,4,4)),--(Data!P:P<DATE(2013,4,6)),Data!Q:Q)

    Re: Basic Yes/No Questionnaire Analysis.


    excellent - glad its worked out, do some dummy runs before next week, or pull in some old sheets and test the results


    any issues come back here


    as its a dashboard maybe you might want to add some conditional formatting for a RAG status (red amber green) on the sheets or on the summary

    Re: Basic Yes/No Questionnaire Analysis.


    THE COUNTIF function does not work across mulitple worksheets - without VBA or you can list all the sheets and use indirect - BUT the formula would need to be changedeach time a new sheet is added - BUT Sum does work across multiple sheets and if we put the sheets between two known sheet names then as sheets are added inbetween it will update automatically


    So in order to do this ,i have named two sheets start and end - now any sheets added inbetween those two sheets will be used in a sum


    SUM(start:end!D13


    will add up all the totals


    inorder to get the YES, NO and N/A - I have just added some helper columns in AA9 , AB9, AC9 and down the rows
    and we use a simple IF statement to change the YES, NO,N/A into a 1
    then we can sum on the summary sheet


    so as new audits are carried out - add a new worksheet, - doesn't matter what you call it - BUT IT MUST be between the Start and End sheet


    i would suggest by copying a master sheet - keep the master sheet outside of the Start/End sheets - can be hidden


    you could modify to add a dropdown so only blank and the required response Yes, No, N/A are available


    Otherwise a bit of VBA to add the sheets to then use indirect fuunction - which is probably outside my skill set at the moment


    see attached


    would that work for you ?


    see attached


    There is a morefunc add in , which will add countIF.3d , but i could not find the download , and its quite old . so not sure about later versions of excel and windows

    Re: Basic Yes/No Questionnaire Analysis.


    not sure how you would do this -

    Quote

    Additional audits are likely to be separate work books

    without knowing the names of the books - also they may need to be open to extract data


    so where would the files be kept directory and what would you name them ?

    Re: Basic Yes/No Questionnaire Analysis.


    how will these additional audits be recorded


    you can count yes and nos - in fact they could be in the same column
    so you could have a column with a drop down for
    Yes/No/NA



    Quote

    "Yes" / "No" / "N/A" results for each question in one place


    will the additional audits be on the same row


    to count


    =countif(range,"Yes")
    so for range you would put the row reference


    Quote

    The point where i'm struggling is that there are to be maybe two or three audits done per day and i don't want to have to alter each formula to include the latest audit.

    So we need to know how that will be laid out for additional audits - before I make any examples

    Re: How to input a value and let a formula calculate the sum of previous entries...


    Quote

    1. What I want is a formula that sums up all entries of the "total D" column from 1 - 7 automatically? Ofc, doing it maunually I'd use =SUM() for the specific range and come to the result of 707, however i need it to be flexible. For example, in order to see what the "total D" is if I key in 29, etc.. I can't always do sums for the specific range that I need. On the other hand the range will always start from the first cell below the "total D" and should therefore be fixed.


    you could use
    =SUMIF(A2:A43,"<="&J2,D2:D43)
    where J2 is the level number - then it will add up everything below or equal to J2 level


    not sure about point 2

    Re: Look up a value in a table and return the value from the same column different ro


    Ok - guest left earlier then expected


    so I have used


    =INDEX(Agents!$B$5:$E$5,SUMPRODUCT((Agents!$B$6:$E$30=A6)*COLUMN(Agents!$B$6:$E$30))-COLUMN(A:A))
    as an array formula - use Control+Shift+Enter to get {} around the formula
    this then uses the table of Agents to lookup the Postcode and return the name into the zone table lists
    if the postcode does not exist - then a #value error is created - which I then use in the next formula - so its ok to have the error in the zones list


    Then on the Map allocations sheet
    I have used
    =IFERROR(VLOOKUP(G6,Zones!$A$5:$D$124,4,FALSE),VLOOKUP(G6,Zones!$A$5:$D$124,2,FALSE))


    this looks up the postcode in the zones list and if no #value found ie no error
    then it will return the value in zones column for the name assigned
    if there is an error - then is uses the 2nd vlookup and returns the zone ID


    I have added the formula on the allocations sheet map, but only in the mauve coloured section
    I have also changed / added a couple of those postcodes onto the names list agents sheet
    to make sure it populated into the zone sheet and onto the allocations sheet OK
    see the darker mauve fill


    see attached file- due to size limits , i have zipped the file up


    does that work for you OK

    Re: Look up a value in a table and return the value from the same column different ro


    you could use just the zones and put the names against each postcode - then use vlookup to find the name and if no name returned return the zone


    if you put a zero in to places with no names


    I'll work on populating the zone table from your list of names sheets automatically - that should be able to work - using an offset formula to get the name listed and then a vlookup with an IF to identify when "no match" returned for a name - the return the zone instead - but maybe tomorrow as commitments to meet right now

    Re: Look up a value in a table and return the value from the same column different ro


    lookup needs to have the data sorted ascending


    are you just looking up the postcode to get the zone ?
    that appears to be the result of in allocation


    you could just use vlookup (postcode, zonelist, 2, false)


    but i suspect you are doing more than that


    you could then also add the names to the postcode on the zonesheet and return names in the allocation