SUMIF - Alternative

[Airbisk]

Hi,

Have a spreadsheet which pulls information from another spreadsheet using the following formula

=SUMIF('[Week 42 Roster.xls]ON CALL'!$I$4,$B132,'[Week 42 Roster.xls]ON CALL'!$I$8)

The data pulled through is a text selection from a drop down list but doesn't pull anything through.

Does the SUMIF work with drop down selections and text values. If not what's a suitable alternative to do this.

Many Thanks

 

[kjv1611]

I think what you need to do is actually point at the same bit of data that the drop-down is pointing to. If the formula will pull anything from a dropdown, I'm pretty sure it'll just pull in what is currently selected in the drop-down/combo box.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

Your question is not clear.

Your use of SUMIF is using 2 of the 3 available arguments.

Does the SUMIF work with drop down selections and text values

SUMIF cannot sum TEXT. If argument 1 is text and argument 2 is a text criteria, then you need a THIRD ARGUMENT RANGE that corresponds to the NUMERIC data.

Typically it would look something like this...
[tt]
=SUMIF(B2:B99,B5,C2:C99)
[/tt]
where column B is text and column C is numeric.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Airbisk]

Thanks chaps,

Its all text and no numeric. There is nothing to be added thats why I don't think SUMIF is the correct function to use.

What the statement tries to do is match two names and if they are the same pulls back the cell contents from another sheet. The cell content will be text. It works fine when the cell has a numeric value even if it doesn't sum anything.

How would I be able to do this with out the SUMIF statement....VLOOKUP?

 

[kjv1611]

Skip can definitely go into further detail on this than I, but it sounds to me like you need to be looking at the INDEX function.

Take a look here:

http://www.contextures.com/xlFunctions03.html

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Please paste an example of your LOOKUP TABLE and the range that this represents.

Please ALSO paste an example of your LOOKUP VALUE and the range this represents.

Please ALSO explain what piece of data you want to return from your VLOOKUP formula.

Please answer ALL these requests.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Airbisk]

Skip, Hope this gives you the information you require:

The data below is an extract from Spreadsheet A, I am trying to fill in column D. The code in this column is similar to:

=SUMIF('[Spreadsheet B]Sheet1'!B1,A2,'[Spreadsheet B]Sheet1'!B2)


StaffName StaffNo Date OnCall1Cust
Ron Galloway 200492 12/10/2009 #VALUE!
Alan Nicholson 201144 12/10/2009 #VALUE!
Anthony Reilly 201007 12/10/2009 #VALUE!

Spreadsheet B (Below) contains the text information in Cell B2, Im hoping to bring back to complete Column D above.

Column A Column B
StaffName Ron Galloway
Customer Carillion

What the code needs to do is match the Staffname between both spreadsheets and bring back the value in the specified cell (a text value).

I have tried VLOOKUP but without success.

Thanks again for looking at this and sorry if its still unclear, not sure how to explain it any better.

 

[GlennUK]

Your spreadsheet B looks like it's arranged strangely. Is this supposed to be a list of StaffName versus Customer? Is there only one entry for each member of staff? If so, then you could arrange it like this:

Column A Column B
StaffName Customer
Ron Galloway Carillion
etc etc

Then VLOOKUP ( or something similar ) will work. E.g.
=VLOOKUP(A2,SheetB!$A$2:$B$99,2,FALSE)



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Airbisk]

Unfortunately I can't change it. The spreadsheet is used as a type of roster and is incorporated into a larger set of spreadsheets that pulls lots of information together for payroll purposes.

The columns represent the days of the week so it looks more like

Day Monday Tuesday
Name Ron Galloway Joe Bloggs
Customer Carillion Amec

Thanks for replying Glenn, because of the spreadsheet setup will what I am trying to do be impossible to do?

 

[GlennUK]

If the data is a regular shape and follows predictable rules, then anything is possible. But it looks like there could be multiple entries per name ( they would appear for more than 1 day, surely ). Can you give a full description of the contents of this sheet?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Airbisk]

Sure, hope this is okay.

Each day in the sheet can only have one entry, basically the sheet is used to record the person on call for that day and only one person per day is used.

Each cell formula in the destination spreadsheet is individually specific to a single cell in the source spreadsheet. No multiple entries possible.

So if the staff name in the destination sheet is the same as the staff name in the specified cell in the source sheet then it should bring back the value in the specified cell in the formula....

Something like =IF(SheetA.Staffname = SheetB.Staffname)THEN SheetB.Cellvalue

 

[GlennUK]

Well, your data is held horizontally, so use HLOOKUP, like:

=HLOOKUP(A1,'Sheet B'!$A$2:$H$3,2,FALSE)

See if that does what you want.

Cheers, Glenn.

Beauty is in the eye of the beerholder.