Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2011, 11:34 PM
jcaswell jcaswell is offline SUMIF Formula question Windows 7 32bit SUMIF Formula question Office 2007
Novice
SUMIF Formula question
 
Join Date: May 2011
Posts: 13
jcaswell is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 05-22-2011, 12:35 AM
macropod's Avatar
macropod macropod is offline SUMIF Formula question Windows 7 32bit SUMIF Formula question Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Try:
=SUMPRODUCT((C15:C45=T10)*(F15:F45))
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-22-2011, 02:17 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline SUMIF Formula question Windows 7 32bit SUMIF Formula question Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #4  
Old 05-22-2011, 02:52 AM
jcaswell jcaswell is offline SUMIF Formula question Windows 7 32bit SUMIF Formula question Office 2007
Novice
SUMIF Formula question
 
Join Date: May 2011
Posts: 13
jcaswell is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF Formula question Need Help With SUMIF Formula In R1C1 Style Todd Excel 9 02-27-2010 08:30 PM
SUMIF Formula question If formula sixhobbits Excel 1 10-02-2009 08:02 AM
SUMIF Formula question Need help with a formula tinkertron Excel 11 04-16-2009 11:43 PM
Categories question & replying with attachment question glitzymama Outlook 0 03-15-2006 09:32 AM
SUMIF Formula question Multiple criteria in SUMIF? pumpkin head Excel 1 02-17-2006 09:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft