Adding Conditional look up to SUMPRODUCT and COUNTIF

[Patty McJorst]

Hi All!

I'm working with a spreadsheet that pulls data from another page. I have all my formulas figured out for when I only have one user, but I struggle when I want to create it for the master list (breaking it down by person's name). Let me give an example:

Worker Name	Number of Cases	Cases Worked yesterday	Total $ Spent	Priority 1 Designation
Doe	3	3	26000.00	0
Obama	1	1	9000.00	1

The numbers are automatically populated with either a COUNTIF function or a SUMPRODUCT function, depending on the criteria. What I need to do now is get them to calculate based on the "Worker Name" from the main sheet.

Any ideas?

Edit: I want to clarify. Some of them are looking things up appropriately while others don't. Here's my current formulas that need help:
Priority: =COUNTIF('[spreadsheet name]'!I2:I200,"1")
Cases worked previous day: =SUMPRODUCT(('[spreadsheet name]'!$AD$1:$FS$1=TODAY()-1)*('[spreadsheet name]t'!$AD$1:$FS$2301="√"))

[ChemistB]

You need to change your COUNTIF to a SUMPRODUCT (since you have 2003) which includes setting your Worker Name range in 'spreadsheet name' = $A2 Or wherever you have the names. You need to add a similar argument to your SUMPRODUCT. Can't be more specific because I don't know where your cells are.

[FDibbins]

hi and welcome to the forum

Try adding an extra condition to your sumproduct....("worker name column"=workername)

[Patty McJorst]

hi and welcome to the forum

Try adding an extra condition to your sumproduct....("worker name column"=workername)

Thanks for the welcome!

I try to add ('[spreadsheet name]'!$A$1:$A$2301=Obama)), as column A is the worker name using, but I get a name error:

=SUMPRODUCT((('[spreadsheet name]'!$AD$1:$FS$1=TODAY()-1)*('[spreadsheet name]'!$AD$1:$FS$2301="√"))*('[spreadsheet name]'!$A$1:$A$2301=Obama))

You need to change your COUNTIF to a SUMPRODUCT (since you have 2003) which includes setting your Worker Name range in 'spreadsheet name' = $A2 Or wherever you have the names. You need to add a similar argument to your SUMPRODUCT. Can't be more specific because I don't know where your cells are.

I have tried to change to a SUMPRODUCT the COUNTIFs, but I think I'm doing something wrong. As for adding the argument in the existing SUMPRODUCTs, I think I might be doing it wrong (see right above your quote).

[ChemistB]

Firstly, Obama would need to be in quotes. However, you probably want to refer to a cell rather than hard coding it like that. Then you can copy it down your sheet (assuming your sheet is organized properly)
So maybe
=SUMPRODUCT((('[spreadsheet name]'!$AD$1:$FS$1=TODAY()-1)*('[spreadsheet name]'!$AD$1:$FS$2301="√"))*('[spreadsheet name]'!$A$1:$A$2301=A2)) (Assuming Obama is in A2)

For the countif

=SUMPRODUCT(--('[spreadsheet name]'!$I$1:$I$2301 ="1"), --('[spreadsheet name]'!$A$1:$A$2301 = A2))

If you're having trouble, upload an example spreadsheet showing your layout without confidential information by "Going Advanced" > "Manage Attachments"

[Patty McJorst]

That worked. I figured out where I was missing a step. Thanks for the help!

[Patty McJorst]

Actually hit another snag along the way. Same problem as before:

=SUMPRODUCT(('[spreadsheet name]'!$H$2:$H$200="value1")+('[spreadsheet name]'!$H$2:$H$200="value2")+('[spreadsheet name]'!$H$2:$H$200="value3"))

So where would I add the *('[spreadsheet name]'!$A$1:$A$2301=A2))? (Obama=A2)