Q: Numbers dilemma
I can't get anywhere on this with Excel for Mac 2008, so I thought I'd try Numbers. Help is greatly appreciated.
I have a database that l'd like to look something like this:
A B C D E F G
1 apple D 1999 0 C 1986 41
2 orange T 2010 44 S 1999 42
3 pear S 2014 0 D 2014 43
4 mango C 1986 0 T 2010 44
I need formulas in Column D to accomplish my objective, which hopefully I can explain coherently:
Looking at the range A1:C4, notice that in cells B2 and C2, the values are T and 2010. Now looking at the range E1:G4, notice that the values that appear in cells E4 and F4 are also T and 2010. There will be only one row in each range in which the T and 2010 combination appear together. And when they match perfectly, as in the above example, I want the value in cell G4 (in this case 44), to be placed in cell D2.
What to do?
iMac, OS X El Capitan (10.11.6)
Posted on Sep 27, 2016 7:44 AM
D1=IF(COUNTA(A1:C1)>2, SUMIFS(G, E, B1, F, C1), "")
this is shorthand for… select cell D1, then type (or copy and paste from here) the formula:
=IF(COUNTA(A1:C1)>2, SUMIFS(G, E, B1, F, C1), "")
select cell D1, copy
select cell D1 thru the end of column D, paste
Posted on Sep 27, 2016 8:13 AM
