Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
I have 2 columns: names in column F and dates in column G.
COL A COL B JET 4/1 JET 4/1 JET 4/1 FRED 4/1 I am trying to calculate the number of times a person completed a project on April 1, so the function needs both their initials and a date filled in, in order to be counted as one. I've tried several countif, sumif and sum product funtions and unable to get any to work. ie. =SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1")) =COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr") =SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0))) JET actually completed 3 projects on 4/1, but I always get a zero total. I tried changing the date format, but that didn't work either. Any help is appreciated. Cheers dblevins |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
Try it this way:
=sumproduct(--(f2:f5="JET"),--(g2:g5=date(2009,4,1))) Regards, Fred "dblevins" wrote in message ... I have 2 columns: names in column F and dates in column G. COL A COL B JET 4/1 JET 4/1 JET 4/1 FRED 4/1 I am trying to calculate the number of times a person completed a project on April 1, so the function needs both their initials and a date filled in, in order to be counted as one. I've tried several countif, sumif and sum product funtions and unable to get any to work. ie. =SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1")) =COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr") =SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0))) JET actually completed 3 projects on 4/1, but I always get a zero total. I tried changing the date format, but that didn't work either. Any help is appreciated. Cheers dblevins |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
You were quite close:
=SUMPRODUCT((F2:F1308="JET")*(MONTH(G2:G1308)=4)) -- Gary''s Student - gsnu200845 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
Excellent! This worked. Thank you so much!
-- DB "Fred Smith" wrote: Try it this way: =sumproduct(--(f2:f5="JET"),--(g2:g5=date(2009,4,1))) Regards, Fred "dblevins" wrote in message ... I have 2 columns: names in column F and dates in column G. COL A COL B JET 4/1 JET 4/1 JET 4/1 FRED 4/1 I am trying to calculate the number of times a person completed a project on April 1, so the function needs both their initials and a date filled in, in order to be counted as one. I've tried several countif, sumif and sum product funtions and unable to get any to work. ie. =SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1")) =COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr") =SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0))) JET actually completed 3 projects on 4/1, but I always get a zero total. I tried changing the date format, but that didn't work either. Any help is appreciated. Cheers dblevins |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
Glad I could help. Thanks for the feedback.
Regards, Fred. "dblevins" wrote in message ... Excellent! This worked. Thank you so much! -- DB "Fred Smith" wrote: Try it this way: =sumproduct(--(f2:f5="JET"),--(g2:g5=date(2009,4,1))) Regards, Fred "dblevins" wrote in message ... I have 2 columns: names in column F and dates in column G. COL A COL B JET 4/1 JET 4/1 JET 4/1 FRED 4/1 I am trying to calculate the number of times a person completed a project on April 1, so the function needs both their initials and a date filled in, in order to be counted as one. I've tried several countif, sumif and sum product funtions and unable to get any to work. ie. =SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1")) =COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr") =SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0))) JET actually completed 3 projects on 4/1, but I always get a zero total. I tried changing the date format, but that didn't work either. Any help is appreciated. Cheers dblevins |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif, SumProduct, CountIf Formula Help | Excel Discussion (Misc queries) | |||
COUNTIF? SUMIF? SUMPRODUCT? IF? | Excel Worksheet Functions | |||
sumif, countif, sumproduct????? | Excel Worksheet Functions | |||
Sumproduct, If, Sumif, Countif, Match?? | Excel Discussion (Misc queries) | |||
countif, sumif, sumproduct - I dont know which to use | Excel Worksheet Functions |