If statement using criteria 3

[harleynana]

Is there a way to sum up all values in a spreadsheet based on two criteria? I am trying to pull in all accts for a specific division. I have a column for the division and if it equals FE, and a column for ACCT. and if it equals 750, How do I get the two criterias combined for one total...I AM PUTTING SOME ADDITIONAL INFORMATION ON THIS POST, the division and account columns have different divisions and accounts, i only want to pull the dollars from a row if the division equals FE AND the account equals 750.

 

[bluedragon2]

Yes, you can use the SUMPRODUCT formula as follows:

=SUMPRODUCT((A1:A1000="FE")*(B1:B1000=750)*(C1:C1000))

subsitute the right columns in the formula that reflect your spreadsheet.

You can take a look at FAQ68-4725 to see how this works.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Zathras]

Take a look in the help file for Database Functions. In particular for this question see DSUM( )

SUMPRODUCT is clever and it works, but Database Functions are a bit more straight-forward to use and understand, once you get over the learning curve.

 

[xlbo]

Gotta disagree Zathras - I'd say that Database functions are faster and probably SHOULD be used but SUMPRODUCT is MUCH easier to understand - especially if you use named ranges eg:
=SUMPRODUCT((DIVISION="FE")*(ACCT=750)*(DOLLARS))

Dunno how you can get much more simple than that !!




Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Zathras]

Depends on how you define "simple" I guess. I would say that

=DSUM(DATABASE,3,CRITERIA)

is rather simple, too. (And it only requires two range names instead of three.)

Granted, DSUM requires giving up some real estate to contain the criteria data, but you might want to do that with the SUMPRODUCT technique too, if for no other reason than to document the selection criteria. Moreover, a frequent source of error when modifying spreadsheets is having hard-coded constants inside formulas.

DSUM:[tt]
DIVISION ACCT
FE 750
[/tt]
SUMPRODUCT:
=SUMPRODUCT((DIVISION=$F$2)*(ACCT=$G$2)*(DOLLARS))
where F2 contains "FE" and G2 contains "750"

"Ya pays yer money, and ya takes yer choice."

 

[bluedragon2]

I believe both formulas are great, but I do like the fact that the SUMPRODUCT references can be anywhere, but the DSUM has to be all together.

Both great functions, and learning the D functions gives you some valuable tools.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

Fair enough Z - can't say I can argue with any of that - it's funny tho - I've yet to find many people who use both. I think it's one of those things that you just stick with what you learnt 1st

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question