Too many criterias...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sephiroths816@gmail.com - 13 Jun 2007 19:53 GMT

Ok, I guess this got sticky because of how detail I'm trying to get it
to be with the multiple criterias. The purpose of this sheet is to
track business expenses amongst about 8 employees.

Assume columns: (with up to a thousand rows of data)
A will consist of Dates
B will consist of Employee Names
C will consist of Transaction Types (i.e. Business lunches, airfare,
etc)
D will consist of the Charge Amounts.

My first obstacle is after meeting the 3 criterias, for a cell to
return the value in column C.
My 2nd obstacle is there are many duplicates. Now there are a lot of
formulas to eliminate them. That's not the case this time.

If row 2 matches all the values in row 1 respectively through columns
A, B, C, and D, then either take the sum of the 2 transactions to show
as one trasnaction type on that day by that employee, or return it in
the next cell and I can just do a sumif function.

Forgive me if i'm not explaining this with complete clarity, but I
guess this is the best I can think to desribe it.

If you have any solutions and/or strategies, please let me know.

Reply to this Message

PCLIVE - 13 Jun 2007 20:16 GMT

Try something like this:

=SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="Criteria3"),D1:D1000)

HTH,
Paul

> Ok, I guess this got sticky because of how detail I'm trying to get it
> to be with the multiple criterias. The purpose of this sheet is to
[quoted text clipped - 21 lines]
>
> If you have any solutions and/or strategies, please let me know.

Reply to this Message

sephiroths816@gmail.com - 13 Jun 2007 20:57 GMT

Thanks Paul,

But I'm not looking to count the amount of fells filled. I need a
summary and/or the value to display.

> Try something like this:
>
[quoted text clipped - 34 lines]
>
> - Show quoted text -

Reply to this Message

Toppers - 13 Jun 2007 20:19 GMT

What exactly are you trying to compute for each employee? Do want a summary
by transaction types for each employee for a given time period?

Reply to this Message

sephiroths816@gmail.com - 13 Jun 2007 20:36 GMT

Yes, I think you've grasped what it is I'm trying to achieve. A
summary is what I'm looking for.

For example, if on January 1, Employee 1 purchased an airline ticket
to visit a client. The amount was $600. Now on that same day, meeting
with the client would mean charging a business lunch at $40 as well as
a dinner at $70. I want to create a user defined sheet with a drop
down list of those employees. For each employee name chosen, I wanted
each type of transaction summarized. Such as, one cell will return the
airfaire of $600. The next to return the total amount of food on that
day of $110, i.e. the lunch and dinner.

Is that asking for too much of Excel?

> What exactly are you trying to compute for each employee? Do want a summary
> by transaction types for each employee for a given time period?
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

PCLIVE - 13 Jun 2007 21:09 GMT

I think you should try this. Unless I'm misunderstanding you, you want a sum
of the amounts in column D that match a specified date, a specified
employee, and a specified transaction type. That is what this will do.

This formula will match the criteria of column A, the date (in this example
is 6/13/2007 which is 39246 when viewed in General format), column B, the
employee name "Employee Bob", and column C, the type of transaction (in this
case "Food"). Column D is then summed for the rows matching the three
criteria.

=SUMPRODUCT(--(A1:A1000=39246),--(B1:B1000="Employee
Bob"),--(C1:C1000="Food"),D1:D1000)

Ultimately the date could be in a cell on your sheet in which you would not
need to use the numeric value...you would just reference the cell that will
contain the date you want. Also, the employee name and possibly even the
type of transaction can exist in a cell in which the formula can refer to
without having to change the formula each time you want to change the
criteria. Example, the date you want to match may be in A1 of the current
sheet. The employee name may be in A2, and the desired transaction type may
be in A3. Let's say your logged data is on sheet 1

=SUMPRODUCT(--(Sheet1!A1:A1000=A1),--(Sheet1!B1:B1000=A2),--(Sheet1!C1:C1000=A3),Sheet1!D1:D1000)

HTH,
Paul

> Yes, I think you've grasped what it is I'm trying to achieve. A
> summary is what I'm looking for.
[quoted text clipped - 43 lines]
>>
>> - Show quoted text -

Reply to this Message

Toppers - 13 Jun 2007 21:16 GMT

OK .... Paul's reply was basically correct:

=SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="Criteria3"),D1:D1000)

For example

Criteria1= Your employee
Criteria2= Type of transaction
Criteria3 = Date of transaction
D1:D1000 = monetary values

So you could have headings in row 1 column B onwards of your transacations
("Airfare","Lunch" ...).
A2=Employee name
A3=Date (?)

If B1="Airfare" then in B2:

=SUMPRODUCT(--($A$1:$A$1000=$A$2),--($B$1:$B$1000=B$1),--($C$1:$C$1000=$A$3),$D$1:$D$1000)

will total for Employee (A2) for expense type (B1) on date (A3)

Copy across for expense types.

HTH

> Yes, I think you've grasped what it is I'm trying to achieve. A
> summary is what I'm looking for.
[quoted text clipped - 41 lines]
> >
> > - Show quoted text -

Reply to this Message

Toppers - 13 Jun 2007 21:21 GMT

.... I got the criteria order wrong, but you get the idea! See Paul's more
complete reply.

> OK .... Paul's reply was basically correct:
>
[quoted text clipped - 67 lines]
> > >
> > > - Show quoted text -

Reply to this Message

sephiroths816@gmail.com - 13 Jun 2007 21:31 GMT

It's ok, Toppers. I was able to to figure it out, being that all I had
to do was adjust the respective cells to the ones in my actual
spreadsheet. The scenario I gave you was just a sample of my actual
spreadsheet. Not only that, they were just one of a few criterias, so
it really doesn't matter what order they are in. Especially with the
solution you gave me. Thanks again.

> .... I got the criteria order wrong, but you get the idea! See Paul's more
> complete reply.
[quoted text clipped - 74 lines]
>
> - Show quoted text -

Reply to this Message

sephiroths816@gmail.com - 13 Jun 2007 21:30 GMT

Thank you the both of you, gentlemen.

And Paul, my mistake. In other spreadsheets I've created, I used the
"sumproduct" to count text data. I didn't stop to think that it would
take sum of numerical values, such as the charge amounts in this case.

Again, thank you.

> OK .... Paul's reply was basically correct:
>
[quoted text clipped - 70 lines]
>
> - Show quoted text -

Reply to this Message

PCLIVE - 13 Jun 2007 21:37 GMT

You're welcome. Sumproduct is a great function.

Thank you the both of you, gentlemen.

And Paul, my mistake. In other spreadsheets I've created, I used the
"sumproduct" to count text data. I didn't stop to think that it would
take sum of numerical values, such as the charge amounts in this case.

Again, thank you.

> OK .... Paul's reply was basically correct:
>
[quoted text clipped - 76 lines]
>
> - Show quoted text -

Reply to this Message

Roger Govier - 13 Jun 2007 22:59 GMT

Hi

This data layout is ideally suited for summarising with a Pivot Table.
Mark your range of data.
Data>Pivot Table>Next>Finish
Drag name to the Page field area
Drag Dates to the Row area
Drag Transaction types to the Row area alongside the Dates
Drag Charge amounts to the Data area.

If you want, right click on Date>Grouping and Outline>Group>Choose
whatever period you want
Days 7 or Days 15 or Month

Select any employee name from the page dropdown and you have your
report.

Signature

Regards

Roger Govier

> Yes, I think you've grasped what it is I'm trying to achieve. A
> summary is what I'm looking for.
[quoted text clipped - 49 lines]
>>
>> - Show quoted text -

Reply to this Message

sephiroths816@gmail.com - 14 Jun 2007 14:42 GMT

Thanks Roger,

I did try the Pivot Table route. However, with the goal I had in mind,
it didn't quite fit for what I was trying to do. Thanks for responding
though.

> Hi
>
[quoted text clipped - 77 lines]
>
> - Show quoted text -

Reply to this Message