Microsoft Office Forums SUMIF Formula question
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-21-2011, 11:34 PM
 jcaswell Windows 7 32bit Office 2007 Novice Join Date: May 2011 Posts: 13
SUMIF Formula question

I want to use the second variable in the SUMIF formula to refer to the contents of a cell rather than a string. So, something like this:

SUMIF(C15:C45,T10,F15:F45)

Where the T10 in the formula refers to the contents of a cell that contains a text string that I want to use as the comparison. I feel that I ought to be able to do this, but cannot find the right syntax.

Can anyone help?
#2
05-22-2011, 12:35 AM
 macropod Windows 7 32bit Office 2007 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 21,212

Try:
=SUMPRODUCT((C15:C45=T10)*(F15:F45))
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
#3
05-22-2011, 02:17 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Hi,
Quote:
 I want to use the second variable in the SUMIF formula to refer to the contents of a cell rather thyan a string.
Suppose your condition is ">100", so if it was in the formula it would look like this:
Code:
`=SUMIF(C15:C45,">100",F15:F45)`
You could then put 100 in T10 and reference it like this:
Code:
`=SUMIF(C15:C45,">"&T10,F15:F45)`
In my experience, that's what most people do.

If you want the > operator in cell T10 too, then you would put this in T10:
Code:
`=">100"`
And then your formula would be
Code:
`=SUMIF(C15:C45,T10,F15:F45)`
If you want to see more examples, here's a tutorial I wrote on SUMIF.
#4
05-22-2011, 02:52 AM
 jcaswell Windows 7 32bit Office 2007 Novice Join Date: May 2011 Posts: 13

Colin

Thank you - the comparison I was trying to do was not mathmatical but textual (i.e. sum if the comparison cell was 'Microsoft'). Thus the ">" (or rather I tried "=" didn't work. However, the article you supplied did help, and what finally worked was SUMIF(G15:G50,""&T15,H15:H50). T15 contained the text I wanted to compare with the array.

Thank you

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Todd Excel 9 02-27-2010 08:30 PM sixhobbits Excel 1 10-02-2009 08:02 AM tinkertron Excel 11 04-16-2009 11:43 PM glitzymama Outlook 0 03-15-2006 09:32 AM pumpkin head Excel 1 02-17-2006 09:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:18 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top