SUMIF based on multiple criteria?

[jumbo1979]

Is it possible to use the sumif function based on two criteria?

ex. of current line:

=SUMIF('All Data'!$Z$2:$Z$10017,"JAP",'All Data'!$D$2:$D$10017)

And I would like to somehow also include AND IF ('All Data'!$AF$2:$AF$10017,"<>*US*") into the above function

let me know

thanks

jumbo

 

[AnotherHiggins]

(I'm going to break this up onto different lines so it fits across the screen. When pasting it into Excel, remove the line breaks)

=SUMPRODUCT(
('All Data'!$Z$2:$Z$10017="JAP")*
(ISERROR(SEARCH("US",'All Data'!$AF$2:$AF$10017)))*
('All Data'!$D$2:$D$10017))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Zathras]

Why not just use DSUM?

Since your ranges begin on row 2, I would infer that you are set up as a "normal" excel list with column headers. If that is so, then you can use a formula like this:
[tt]
=DSUM(DATABASE,"POPULATION",CRITERIA)
[/tt]
I have assumed the column names of "POPULATION" for column D, "CODE" for column Z and "EXTRA" for column AL.

Create a range name "DATABASE" for your data (E.g., 'All Data'!$A$2:$AF$10017)
Put the following in four cells and assign the range name "CRITERIA":
[tt]
CODE EXTRA
JAP <>US*
[/tt]
The formula will then sum the values from column with heading "POPULATION" for every row that has "JAP" in the "CODE" column and does not have a value that begins with "US" in the "EXTRA" column.

Of course, it is still possible to use SUMIF, but once you learn how to use database functions, they are much easier to understand.

 

[Zathras]

Sorry, I missed the first asterisk. Use this for the criteria instead:
[tt]
CODE EXTRA
JAP <>*US*
[/tt]
BTW, you don't have to use range names, you could just use something like
[tt]
=DSUM('All Data'!$A$2:$AF$10017,4,$BB$1:$BC$2)
[/tt]
but range names make it much easier to understand and maintain.

Just consider what you will have to do when the requirements change and in addition to JAP / non-*US* you have to include XYZ / non-*US*...
With the DSUM all you have to do is change the CRITERIA range to include three lines that look like this:
[tt]
CODE EXTRA
JAP <>*US*
XYZ <>*US*
[/tt]
The formula itself stays the same. I don't even want to think about what the SUMPRODUCT version would look like.

 

[AnotherHiggins]

I like my sumproduct.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Zathras]

 

[xlbo]

Sumproduct is good for doing occasional complex sums

DSUM should really be used if you need to do extensive database style queries (that's really what we are doing here isn't it....SELECT sum(this measure) FROM Worksheet.Table where these criteria apply)

It is more efficient and as Zathras suggests, should be easier to add criteria to in the future.

Personally, I would not use DSUM if I had less than 20 or 30 formulae. Above that and sumproduct starts to crank a bit - guess what I'm saying is to pick the right tool for the job !!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question