Using SUMIF to count certain criteria but exclude others

Hello

Hoping someone can help. I'm working with some data and want to use something similar to SUMIF, but want to count certain criteria but exclude others. Let me try and give an example

My 2 columns may look something like the following where column A is my result code and B is the volume.

A B
IX1N 12
IX1S 21
ICON 8
ICPP 2
IX2N 26
IX2S 5
IXAS 65
IXEN 14
IXES 15
ISN3 4
ISJP 20


For the purposes of this I'm going to use the ? symbol as a single character wild card. What I would like to do is count anything that is IC?? or IS?? but then to exclude anything that is ICP? ISN? or ICON

Can anyone help please.

Thanks

Originally Posted by Ron Coderre

It's really important to post the version of Excel that you are using due to the differences in functionality.

If you have Excel 2007 or later:
=COUNTIFS(A1:A11,"IC*",A1:A11,"<>ICP",A1:A11,"<>ICON")

Otherwise, try something like this:
=COUNTIF(A1:A11,"IC*")-SUM(COUNTIF(A1:A11,{"ICP*","ICON*"}))
or this:
=COUNTIF(A1:A11,"IC*")-SUM(COUNTIF(A1:A11,{"ICP","ICON"}))

Does that help?

Hi,

Thanks for this. I'm using excel 2000 by the way. This doesn't quite work as I need to sum the numbers up in column B rather than counting them. I've tried a few variations of using something like =SUMIF(A1:A11,"IC*")-SUM(SUMIF(A1:A11,"ICP","ICON")) but none of them have worked.

You aren't using SUMIF() correctly. You have left out the 3rd argument which is the array to be summed. By omitting it the sum is made on the first argument and because that is text the sum will be 0.

So, instead of =SUMIF(A1:A11,"IC*") which sums A1:A11 you should have =SUMIF(A1:A11,"IC*",B1:B11) which sums B1:B11

Does that get you going in the right direction?

Here's a link to understand the SUMIF() function:
http://www.excelfunctions.net/ExcelSumif.html