Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Count of Criteria Every 3rd Row
Hi All,
I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY IF the value(s) equals a specific criteria. Example: Sum Count of Every 3rd Row that has a value of 50. The criteria will vary. Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200512/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Count of Criteria Every 3rd Row
if I read your question right you want to sum the occurances of 50, but only
those in every 3rd row. So, for the range of a1:a10, summing those 50's in rows 1,4,7,10 =SUMPRODUCT((A1:A10=50)*(MOD(ROW(A1:A10)-1,3)=0)*(A1:A10)) you could refer to cells with the values of 50 and 3 in the formula if desired vs the hard coding of the values in the formula "Sam via OfficeKB.com" wrote: Hi All, I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY IF the value(s) equals a specific criteria. Example: Sum Count of Every 3rd Row that has a value of 50. The criteria will vary. Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200512/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Count of Criteria Every 3rd Row
Assuming that A1:A100 contains your data, to count every third row,
starting with the first cell in the range, try... =SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),--(A1:A100=B1)) ....where B1 contains your criteria. To count every third row, starting with the third cell in the range or the first occurrence of third, change the +0 bit to +1. Hope this helps! In article <5898fe5a1a483@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY IF the value(s) equals a specific criteria. Example: Sum Count of Every 3rd Row that has a value of 50. The criteria will vary. Cheers, Sam |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Count of Criteria Every 3rd Row
Hi Duane,
Thank you for reply. I've tried the solution below, but do not get the expected answer. Not sure why? =SUMPRODUCT((A1:A10=50)*(MOD(ROW(A1:A10)-1,3)=0)*(A1:A10)) Cheers, Sam duane wrote: if I read your question right you want to sum the occurances of 50, but only those in every 3rd row. So, for the range of a1:a10, summing those 50's in rows 1,4,7,10 =SUMPRODUCT((A1:A10=50)*(MOD(ROW(A1:A10)-1,3)=0)*(A1:A10)) you could refer to cells with the values of 50 and 3 in the formula if desired vs the hard coding of the values in the formula Hi All, [quoted text clipped - 6 lines] Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200512/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Count of Criteria Every 3rd Row
Hi Domenic,
Thank you for reply. The Formula works Great! =SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),--(A1:A100=B1)) Cheers, Sam Domenic wrote: Assuming that A1:A100 contains your data, to count every third row, starting with the first cell in the range, try... =SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),--(A1:A100=B1)) ...where B1 contains your criteria. To count every third row, starting with the third cell in the range or the first occurrence of third, change the +0 bit to +1. Hope this helps! Hi All, [quoted text clipped - 6 lines] Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200512/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Count of Criteria Every 3rd Row
Hi,
Try the following array formula (Ctrl+Shift+Enter). Assume your data is in range A13:A17 23 34 56 45 34 In A19, enter the following array formula (Ctrl+Shift+Enter) =SUM(IF((MOD(ROW(A13:A17),2)=0*(A13:A174)),$A$13: $A$17,0)) The result of this will be 79. If you have any problems please feel freet o contact me at Regards, "Sam via OfficeKB.com" wrote: Hi All, I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY IF the value(s) equals a specific criteria. Example: Sum Count of Every 3rd Row that has a value of 50. The criteria will vary. Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200512/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count cell if value present in every other cell + criteria | Excel Worksheet Functions | |||
I need to count while using multiple criteria | Excel Worksheet Functions | |||
count cells using multiple criteria | Excel Discussion (Misc queries) | |||
Count using complex criteria | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |