Counting column totals for specified data

[celestersue]

I am needing to total the data in a column only if certain fields equal the correct representative's name in that row. For example:
If C2, C5, C12 and C33 = Todd, AND E2, E5, and E12 have any data entered THEN total the amounts in G2, G5, and G12 (but not G33 because E33 was an empty cell).
What formula would help me accomplish this?
Thanks!

[ConneXionLost]

Do you want to check each cell in column C or just those four cells?

[celestersue]

Yes, check each cell in that column.

[ConneXionLost]

How about:

Please Login or Register  to view this content.

[Tony Valko]

For "not equal to blank" you can simply use "<>".

=SUMIFS(G:G,C:C,"Todd",E:E,"<>")

[celestersue]

I've tried both of those formulas, however it's not totaling the amounts in those columns for the rows that have the correct information. Is there another way to get this data to pull in?

I've used a formula similar to find the content and display it if it correlates to holding the correct info, which is what I need except instead of populating a cell with this info I need it to do a sum tally of one of the columns data.
Example: =IF('All Data'!$C2= "Todd",'All Data'!A2)

[Tony Valko]

Can you post a SMALL sample file so we can see what your data looks like?

[celestersue]

I have attached an example of what I am looking to have done. Hopefully this helps to understand what I need. I didn't mention it before, but I would like to break it up by month as well.

Thanks!

[Tony Valko]

OK, the problem is that the SUMIFS function won't work when referencing another file unless that other file is open.

Better to use the SUMPRODUCT function for this application. It will work on closed files.

Here's your current SUMIFS formula in cell DQ2:

=SUMIFS('C:\A downloaded Excel files\[iLink Loan Officer Performance Report.xlsx]All Data'!BK:BK,'C:\A downloaded Excel files\[iLink Loan Officer Performance Report.xlsx]All Data'!BD:BD,"Chriserrod",'C:\A downloaded Excel files\[iLink Loan Officer Performance Report.xlsx]All Data'!BU:BU,"<>"&"")

Here's the SUMPRODUCT version of that formula:

=SUMPRODUCT(--('C:\A downloaded Excel files\[iLink Loan Officer Performance Report.xlsx]All Data'!BD2:BD100="Chriserrod"),--('C:\A downloaded Excel files\[iLink Loan Officer Performance Report.xlsx]All Data'!BU0:BU100<>""),'C:\A downloaded Excel files\[iLink Loan Officer Performance Report.xlsx]All Data'!BK2:BK100)

You should avoid using entire columns as range references when using the SUMPRODUCT function. Use smaller specific ranges.

[celestersue]

Sorry I added the wrong file (see attached). I'm actually referencing the other sheet in the workbook. Does that make a difference?

[Tony Valko]

Can you tell us exactly what you want to sum based on the data in that file?

[celestersue]

I want to sum the dollar amount for the leads within the specified month range (data in column T) that have a dollar value amount entered in "Loan Amount" field (column J) by Rep name (Todd, Chris, etc).

[Tony Valko]

Hmmm...

Your description doesn't match anything in the Test Example 2 file.

On the Data sheet column T is empty. On the Totals sheet column T is empty except for the column header Aug-13.

On the Data sheet column J has the column header Campaign and the column contains text entries. On the Totals sheet column J is empty except for the column header Nov-13.

[celestersue]

I apologize for my disorganization in communicating this. H not J, and R not T.

[Tony Valko]

Sorry, I can't make heads or tails out of your file.