• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If question

Simon Lee

Simon Lee

New Member
Hi There

If is have a range of cells with the text value 5400/001 in them I would like to sum the corresponding cell with a numeric value in them, and ignore cells in the range that have other text vakues such as 3200/000 etc. I tried the normal =SUMIF(A7:A132,"5400/001",B7:B132)

but this keeps throwing out an error in the range

I'm running excel 2010
 
Simon,

So if you have two cells with 5400/001
what should the answer be?

10800/001 or 10800/002 or 10800 or something else?
 
Hi Hui

The sample set is like this:

A B
1 5450/001 -1056,4
2 3900/000 -83,96
3 5450/001 -637,54
4 4560/000 -238,15
5 5450/001 -854,57
6 5450/001 -104,99
7 3200/000 -149
8 3200/000 -20
9 3200/000 -1,05



TOTAL OF ALL 5450/001 = -2653,5
TOTAL OF ALL 3900/000 = -83,96
TOTAL OF ALL 4560/000 = -238,15
TOTAL OF ALL 3200/000 = -170,05


Make sense?
 
=SUMIFS(B1:B10,A1:A10,"5450/001")
or
=SUMIFS(B1:B10,A1:A10,A1)
works for me
 
Hi Hui

Have alook at the uploaded file, with your solutions, they still throw out the same error in the range.

Maybe I'm missing something really simple here? Maybe the formatting of the actual sheet?
 

Attachments

Check for leading or trailing spaces
Copy the text eg: 5450/001 from the list to the formula

Bed time here so I'll look tomorrow
 
Odd, I downloaded your file and am able to use SUMIF & SUMIFS without issue.

Both by Cell Reference & Hardcoded.
 

Attachments

Chihiro

Can you upload the file for me so that I can open it this side. If it still won;t work then at least I know its purely a setting that isn;t correct my side and I then can work i=on identifying it.
 
Hi Simon,
I am also having No Issue with XP, Excel 2007.

Check if you have any error or #N/A in your range.

Regards,
 
You must log in or register to reply here.
Back
Top