|
Sum Count of Two Numbers in the same Row
Hi All,
Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell houses numeric single-digit or double-digit values. I would like a Formula to find / match all instances of any two particular numbers (single-digit / double-digit) in the same Row and Sum the Count of their total occurrences together. For instance, how many times, if any, does 80 and 87 appear together in the same Row. I would like to refer to the values using two cell references. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
Assuming that A1 contains your first criterion, such as 80, and B1
contains your second criterion, such as 87, try... =SUM(--(MMULT(--(Results=A1),TRANSPOSE(COLUMN(Results)^0))*MMULT(--(Resul ts=B1),TRANSPOSE(COLUMN(Results)^0))0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi All, Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell houses numeric single-digit or double-digit values. I would like a Formula to find / match all instances of any two particular numbers (single-digit / double-digit) in the same Row and Sum the Count of their total occurrences together. For instance, how many times, if any, does 80 and 87 appear together in the same Row. I would like to refer to the values using two cell references. Thanks Sam |
Hi Don,
Don Guillett wrote: shouldn't a simple =COUNTIF( do it for you? I don't get the expected results using a simple =COUNTIF. Could you, possibly provide an example based on: Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell houses numeric single-digit or double-digit values. I would like a Formula to find / match all instances of any two particular numbers (single-digit / double-digit) in the same Row and Sum the Count of their total occurrences together. For instance, how many times, if any, does 80 and 87 appear together in the same Row. I would like to refer to the values using two cell references. Much appreciated. Cheers, Sam Don Guillett wrote: shouldn't a simple =COUNTIF( do it for you? Hi All, [quoted text clipped - 9 lines] Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
Do the two numbers always occur consecutively in the row?
Do the two numbers only appear together once in the row? If so, try the following instead... Assumptions: D1:H10 contains your 5 columns of data A1 contains your first criterion, such as 80 B1 contains your second criterion, such as 87 Formula: =SUM(MMULT((D1:G10=A1)*(E1:H10=B1),TRANSPOSE(COLUM N(D1:G10)^0))) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi All, Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell houses numeric single-digit or double-digit values. I would like a Formula to find / match all instances of any two particular numbers (single-digit / double-digit) in the same Row and Sum the Count of their total occurrences together. For instance, how many times, if any, does 80 and 87 appear together in the same Row. I would like to refer to the values using two cell references. Thanks Sam |
Hi Domenic,
Thank you very much for assistance and a brilliant Formula. The Formula is returning the expected results. Is it possible to tweak the Formula based on my matrix type layout below. The results are being returned to a chart / matrix layout: I have the criterion vertically and horizontally and then they are referenced using the horizontal and vertical cell address that houses the criterion, and the result is returned to the intercept of the vertical and horizontal criterion. So at some point both criterion values being referenced will be the same, if possible can the Formula return a zero when this occurs? Example: Cell Ref. A2 and B1 criterion 80 and 80 Cell Ref. A3 and C1 criterion 81and 81 Criteria B1 houses 80, C1 houses 81, D1 houses 82, E1 houses 83, etc A2 houses 80 0 4 9 7 A3 houses 81 5 0 3 2 A4 houses 82 7 3 0 1 A5 houses 83 2 7 5 0 So, I would use your Formula with the corresponding vertical and horizontal cell references (and copy across and down): =SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*MMULT(-- (Results=B$1),TRANSPOSE(COLUMN(Results)^0))0)) ....confirmed with CONTROL+SHIFT+ENTER. Cheers, Sam Domenic wrote: Assuming that A1 contains your first criterion, such as 80, and B1 contains your second criterion, such as 87, try... =SUM(--(MMULT(--(Results=A1),TRANSPOSE(COLUMN(Results)^0))*MMULT(--(Resul ts=B1),TRANSPOSE(COLUMN(Results)^0))0)) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Hi All, [quoted text clipped - 9 lines] Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
Hi Domenic,
Just replied to your earlier Post. Thanks for further input. Domenic wrote: Do the two numbers always occur consecutively in the row? No Do the two numbers only appear together once in the row? Yes If so, try the following instead... Assumptions: D1:H10 contains your 5 columns of data A1 contains your first criterion, such as 80 B1 contains your second criterion, such as 87 Formula: =SUM(MMULT((D1:G10=A1)*(E1:H10=B1),TRANSPOSE(COLU MN(D1:G10)^0))) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Cheers Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
Try...
=IF($A2<B$1,SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*M MULT(--(Results=B$1),TRANSPOSE(COLUMN(Results)^0))0)),0) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi Domenic, Thank you very much for assistance and a brilliant Formula. The Formula is returning the expected results. Is it possible to tweak the Formula based on my matrix type layout below. The results are being returned to a chart / matrix layout: I have the criterion vertically and horizontally and then they are referenced using the horizontal and vertical cell address that houses the criterion, and the result is returned to the intercept of the vertical and horizontal criterion. So at some point both criterion values being referenced will be the same, if possible can the Formula return a zero when this occurs? Example: Cell Ref. A2 and B1 criterion 80 and 80 Cell Ref. A3 and C1 criterion 81and 81 Criteria B1 houses 80, C1 houses 81, D1 houses 82, E1 houses 83, etc A2 houses 80 0 4 9 7 A3 houses 81 5 0 3 2 A4 houses 82 7 3 0 1 A5 houses 83 2 7 5 0 So, I would use your Formula with the corresponding vertical and horizontal cell references (and copy across and down): =SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*MMULT(-- (Results=B$1),TRANSPOSE(COLUMN(Results)^0))0)) ...confirmed with CONTROL+SHIFT+ENTER. Cheers, Sam Domenic wrote: Assuming that A1 contains your first criterion, such as 80, and B1 contains your second criterion, such as 87, try... =SUM(--(MMULT(--(Results=A1),TRANSPOSE(COLUMN(Results)^0))*MMULT(--(Resul ts=B1),TRANSPOSE(COLUMN(Results)^0))0)) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Hi All, [quoted text clipped - 9 lines] Thanks Sam |
Hi Domenic,
Great! Thank you so much. Domenic wrote: Try... =IF($A2<B$1,SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*M MULT(--(Results=B$1),TRANSPOSE(COLUMN(Results)^0))0)),0) ...confirmed with CONTROL+SHIFT+ENTER. Cheers, Sam Domenic wrote: Try... =IF($A2<B$1,SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*M MULT(--(Results=B$1),TRANSPOSE(COLUMN(Results)^0))0)),0) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Hi Domenic, [quoted text clipped - 48 lines] Thanks Sam -- Message posted via http://www.officekb.com |
| All times are GMT +1. The time now is 02:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com