Excel, counting based on conditions frrom neighboring cells.

[JannaFeeley]

I have an excel workbook that contains four different sheets. Within
each of these sheets, there are two columns, one entitled "Status,"
containing entries of each "Open" or "Closed," the other entitled
"Dates," containing assorted dates. What I need to do is count the
number of "Open" and "Closed" entries for each of the dates, no matter
what worksheet they are in. Some dates are repeated in mutiple work
sheets, but the sum should be a grand total. Combining the worksheets
is not an option.

For example, I have 5 "Open" statuses for the date "July 20th", but 2
of these are in the first worksheet, 1 is in the second, and 1 is in
the 4th. Does anyone know of a way I can count these?

Also, to make matters more complicated, I would like the tally to
update autmatically, since multiple people add to the sheet. For
instance, if someone were to add an "Open" "July 20th" to any of the
worksheets, I would like the tally to increase automatically.

Thanks for any advice!!

 

[JannaFeeley]

I have an excel workbook that contains four different sheets. Within
each of these sheets, there are two columns, one entitled "Status,"
containing entries of each "Open" or "Closed," the other entitled
"Dates," containing assorted dates. What I need to do is count the
number of "Open" and "Closed" entries for each of the dates, no matter
what worksheet they are in. Some dates are repeated in mutiple work
sheets, but the sum should be a grand total. Combining the worksheets
is not an option.

For example, I have 5 "Open" statuses for the date "July 20th", but 2
of these are in the first worksheet, 1 is in the second, and 1 is in
the 4th. Does anyone know of a way I can count these?

Also, to make matters more complicated, I would like the tally to
update autmatically, since multiple people add to the sheet. For
instance, if someone were to add an "Open" "July 20th" to any of the
worksheets, I would like the tally to increase automatically.

Thanks for any advice!!

One way it might work is if I could get each additional entry in each
of the 4 worksheets to append to another list entirely. Then I could
simply tally the entries from this list. However, since there are
other categories of information associated with each entry that i do
not wish to tally (i.e., other than Status and Date), I am not sure
how to go about this.

 

[Shane Devenshire]

Hi,

Assume the dates are always in column A and the Open/Closed is in column B
of each sheet. On sheet1 list all the possible dates, in the following
example, in column E of sheet1.

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$20),--("Open"=Sheet2!$B$1:$B$20))+SUMPRODUCT(--(E1=Sheet3!$A$1:$A$20),--("Open"=Sheet3!$B$1:$B$20))+
SUMPRODUCT(--(E1=Sheet4!$A$1:$A$20),--("Open"=Sheet4!$B$1:$B$20))+
SUMPRODUCT(--(E1=Sheet5!$A$1:$A$20),--("Open"=Sheet5!$B$1:$B$20))

Adjust the ranges.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[JannaFeeley]

Hi,

Assume the dates are always in column A and the Open/Closed is in column B
of each sheet.  On sheet1 list all the possible dates, in the following
example, in column E of sheet1.

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$20),--("Open"=Sheet2!$B$1:$B$20))+SUMPRODU­CT(--(E1=Sheet3!$A$1:$A$20),--("Open"=Sheet3!$B$1:$B$20))+
SUMPRODUCT(--(E1=Sheet4!$A$1:$A$20),--("Open"=Sheet4!$B$1:$B$20))+
SUMPRODUCT(--(E1=Sheet5!$A$1:$A$20),--("Open"=Sheet5!$B$1:$B$20))

Adjust the ranges.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire






- Show quoted text -

Excel seems to be accepting the equation, but I am not getting any
values other than "0."

 

[JannaFeeley]

Excel seems to be accepting the equation, but I am not getting any
values other than "0."- Hide quoted text -

- Show quoted text -

I must have made a mistake, because it just worked! Thanks so much!