Using AND function, how do I sum the remaining data?

[Guest]

Using either AND function, or nested IF functions, I'm trying to sum data in
a third column for only those items that meet both of the requirements in the
AND function. I can manage to sum the entire column. Any ideas? Thanks

 

[Guest]

Do you have an example of the data you are using?

 

[Guest]

60-22677 IL 2 74
60-22799 IL 0
60-22775 WI 7
60-22444 IL 4
60-22804 WI 1
60-22799 WI 0
60-22677 IL 1

The first column represents a manager ID number. The second column
represents location. The third column represents the number of items being
measured. Some managers overlap states. What I'd like the formula to do is
first ID manager AND state, and then sum the items in column 3 for only those
data sets which are (true,true). Does that help clarify? Thanks!

 

[Guest]

I'm not clear as to what the requirements to get a sum are. What are the
items that should be (true,true) ?

 

[Guest]

You'll see in column 1 that there are various manager IDs. In column 2,
various state locations. We want to be able to measure how many items belong
to a given manager, contingent upon location as well.

Example:

If I want to find the number of items for manager 60-22799 in WI only (row
6), the answer would be 4. In the case of the other reference to manager
60-22799 (row 2), the totality of the AND statement would be false as the
other incident of this manager showing up in the table is tied to IL and
would therefore be eliminated (true manager #, false state location). How do
I sum data for only specific managers in specific locations?

 

[Mark Bigelow]

Hi Leona,

Here's how you want to do it:

=SUM(IF(A$1:A$7="60-22799", IF(B$1:B$7="IL",C$1:C$7, 0), 0))

After typing that formula in the cell (or pasting it), you must click
Ctrl+Enter instead of just enter, as it is an array formula. Also, if
you change, you must make sure that all references must be of the same
size, so if you reference A1:A10, you must reference B1:B10 and C1:C10.
Let me know if you have any questions.

Mark

 

[Guest]

THANKS MARK! I'll try that. Appreciate your help.

 

[Guest]

Mark --

We've tried and are still not getting a valid response. We've done a test
so that we can determine what the valid response should be. Instead of
coming up with the numeric value, we're getting a zero. It shouldn't be zero
(as in no items counted), so it must be an indicator of a false in the nested
if functions somewhere. Any ideas? Thanks

 

[Guest]

If you have a lot of data and it always added to then I would suggest
linking it to a access database where you can do a sum query that would
always give you the totals no matter how much data you entered into the
spreadsheet. This way you would not have to be constantly changing the
formula and would not need a seperate formula for each manager. If you need
help on how to set the database and linkage up let me know.

 

[Guest]

Hi,
Try:

=sum((A1:A10="60-22677")*(B1:B10="IL")*(C1:C10)) and enter as array formula
as before

HTH

 

[Guest]

Thanks -- tried it and am still coming up with an invalid sum. The test
spread I worked up should total 11 for that manager in IL. It's coming up
with zero. Any other ideas? Thanks!

 

[Guest]

Mark --

ATTABOY!!!!!!!!! Using your formula and Help through Excel, I found that I
must use Shift+Control+Enter to enter the Array formula. The formula works
brilliantly. Thanks so much for your time -- appreciate it!

 

[Guest]

I tried it with the data from your note and it worked OK. I got an answer of
3 for ID of 60-22677 and IL - that's correct?.

Stating the obvious but check that the ID and State exist in the data and/or
SUM formula. If they don't you will get an answer of 0. An extra blank on the
field e.g '60-22677b ' will give also give a result of 0. (b=blank)