I have a spread sheet with a huge list of numbers. Some of the numbers have mutiple instances, IE 493 493.1 493.99 493.22
Im trying to use the following formula.
I tried using an astrix but that didnt work. How can I get it to SUM multiple versions of the number ?=SUMIF(E2:E645,"493",F2:F645)
Thank you
Last edited by itsnick; 09-07-2010 at 04:54 PM.
So you want to sum the values in F2:F65 where E2:E645 contains the number 493?
If all that is in the cell is 493, then your formula should work.
If you want to count cells that contain 493 with some other text... like X493xx
then
=SUMIF(E2:E645,"*493*",F2:F645)
if there are also cells that just contain 493 as well as mixed alphanumerics..
then first select the column and go to Data|Text to columns... click Next, click Next again... then choose Text from the column data format section and click Finish.. then apply formula.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
The numbers im trying to add to the sum are 493 493.01 493.2 493.9 to give you an idea
E F
493 2
200 1
330 3
493.2 6
493.01 2
100 2
242 4
I tried the astrix but it didnt work, the formula is giving me 0
You will need sumproduct, I think
=SUMPRODUCT(--(INT(E2:E645)=493),F2:F645)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I used the forumla and it gave me an #value error.
I modified it to
=SUMPRODUCT(--(INT(E2:E645=493)),F2:F645)
but this only sums the value for 493
Do you have any text string within the range of E2:E645 or any VALUE errors in that range?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Here is a sample
203
153.9
427.31
183
401.9
719.41
414.01
174.5
786.5
174.9
157.9
496
414
V57.1
V76.12
702
V72.84
327.23
185
V57.89
726
238.2
780.2
786.2
724.02
Last edited by itsnick; 09-07-2010 at 04:56 PM.
The values that start with "V" are text values - when you use INT on those it gives you an error, assuming you want to ignore those try like this
=SUMIF(E2:E645,">=493",F2:F645)-SUMIF(E2:E645,">=494",F2:F645)
or if you have Excel 2007 or later you can use SUMIFS
=SUMIFS(F2:F645,E2:E645,">=493",E2:E645,"<494")
Audere est facere
Try:
=SUMPRODUCT(--(INT(SUBSTITUTE(E2:E645,"V","")+0)=493),F2:F645)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That worked in my 2003, when you use two sumifs like that do you seperate them with - or are u actually subtracting.
EDIT
they both worked
if I wanted to find one of the values starting with V I could probably just use thes astrix ? *
You are actually subtracting to SUMIF results.
Note: I am not sure if any of the V entries may have 493 in them like V493.45, if so the SUMIFs don't work, and the last SUMPRODUCT I posted does (and even if the V493.5's don't exist the SUMPRODUCT works...... not sure which suits you best.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thank you NB will be donating to th Lupus canada for your help
Thank you... that is very kind of you
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks