AND, IF, and SUM IF Functions
 

I am trying to write a simple code for a time log file in excel. on
sheet1("time log") i have four columns (headers are Date, Client, Time,
Staffer). on sheet2 ("totals"), column A has a list of all of the clients and
columns B through M have the months of the year.
i would like for people to be able to enter in all of the info into sheet1
and then for sheet2 to automatically total the hours according to the right
client and under the right month in which the services were performed.
I have tried the following formula but it doesn't seem to work properly:
=IF((MONTH(C$2)=(MONTH('Time Entry'!$A:$A))),SUMIF('Time
Entry'!$B:$C,$A15,'Time Entry'!$C:$C),)

can someone help me with this??

AND, IF, and SUM IF Functions
 

=SUMPRODUCT(--(MONTH('Time Entry'!$A$2:$A$500)=MONTH(C$2)),--('Time
Entry'!$B$2:$B$500=A15),'Time Entry'!$C$2:$C$500)


note that you cannot use the whole column in this formula (A:A)

also note that if C2 is empty and any entry in A is empty than that
condition is TRUE since Excel count blanks a Jan


--
Regards,

Peo Sjoblom


"hotelmasters" wrote in message
...
I am trying to write a simple code for a time log file in excel. on
sheet1("time log") i have four columns (headers are Date, Client, Time,
Staffer). on sheet2 ("totals"), column A has a list of all of the clients
and
columns B through M have the months of the year.
i would like for people to be able to enter in all of the info into sheet1
and then for sheet2 to automatically total the hours according to the
right
client and under the right month in which the services were performed.
I have tried the following formula but it doesn't seem to work properly:
=IF((MONTH(C$2)=(MONTH('Time Entry'!$A:$A))),SUMIF('Time
Entry'!$B:$C,$A15,'Time Entry'!$C:$C),)

can someone help me with this??

AND, IF, and SUM IF Functions
 

thanks - but it doesn't seem to be working. the data is not filtering into
the correct months. any other suggestions?

thanks.

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(MONTH('Time Entry'!$A$2:$A$500)=MONTH(C$2)),--('Time
Entry'!$B$2:$B$500=A15),'Time Entry'!$C$2:$C$500)


note that you cannot use the whole column in this formula (A:A)

also note that if C2 is empty and any entry in A is empty than that
condition is TRUE since Excel count blanks a Jan


--
Regards,

Peo Sjoblom


"hotelmasters" wrote in message
...
I am trying to write a simple code for a time log file in excel. on
sheet1("time log") i have four columns (headers are Date, Client, Time,
Staffer). on sheet2 ("totals"), column A has a list of all of the clients
and
columns B through M have the months of the year.
i would like for people to be able to enter in all of the info into sheet1
and then for sheet2 to automatically total the hours according to the
right
client and under the right month in which the services were performed.
I have tried the following formula but it doesn't seem to work properly:
=IF((MONTH(C$2)=(MONTH('Time Entry'!$A:$A))),SUMIF('Time
Entry'!$B:$C,$A15,'Time Entry'!$C:$C),)

can someone help me with this??

AND, IF, and SUM IF Functions
 

The formula works so either I misunderstood what you are trying to do or
your data is not matching

My formula will sum all the entries in C2:C500 where the month in A2:A500 =
the month in C2 AND where B2:B500 = A15

of course if the values in either C2 or A2:A500 are text like January as
opposed to entries like 01/12/07, then it won't work but then again you
would get a value error



--
Regards,

Peo Sjoblom


"hotelmasters" wrote in message
...
thanks - but it doesn't seem to be working. the data is not filtering into
the correct months. any other suggestions?

thanks.

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(MONTH('Time Entry'!$A$2:$A$500)=MONTH(C$2)),--('Time
Entry'!$B$2:$B$500=A15),'Time Entry'!$C$2:$C$500)


note that you cannot use the whole column in this formula (A:A)

also note that if C2 is empty and any entry in A is empty than that
condition is TRUE since Excel count blanks a Jan


--
Regards,

Peo Sjoblom


"hotelmasters" wrote in message
...
I am trying to write a simple code for a time log file in excel. on
sheet1("time log") i have four columns (headers are Date, Client, Time,
Staffer). on sheet2 ("totals"), column A has a list of all of the
clients
and
columns B through M have the months of the year.
i would like for people to be able to enter in all of the info into
sheet1
and then for sheet2 to automatically total the hours according to the
right
client and under the right month in which the services were performed.
I have tried the following formula but it doesn't seem to work
properly:
=IF((MONTH(C$2)=(MONTH('Time Entry'!$A:$A))),SUMIF('Time
Entry'!$B:$C,$A15,'Time Entry'!$C:$C),)

can someone help me with this??