Hi, I need a formula to sum an amount based on row and columns perimeters.
I have the month-year in the columns (B3:Z3) and numbers in the rows (B12:B55), so the amounts are obviously in B12:Z55.
Curve ball: I have a range of cells in the destination tab that contain the list of numbers to be summed for that particular category (T26:AA26).
I need a formula that would sum all dollars for Feb 2018 for the numbers in range T26:AA26. So the reference range (T26:AA26) I would have entered the number 2 in T26 and the number 4 in U26. The formula would return 50.
Jan 18 Feb 18 Mar 18 Apr 18
1 40 5
2 30 20
3 10 5 20
4 20 3 2
This is the formula I have right now. The reference numbers I mentioned earlier are in T26:Z26. So the below formula manually adds those together, one by one. I would like to have one formula that will sum based on the numbers in T26:Z26. Something like "*"&T26:Z26&"*"?
(Must press Cntrl+Shift+Enter)
=SUM(IF('Revenue'!$B$12:$B$53=$T26, IF('Revenue'!$AA$7:$AQ$7=F$8, 'Revenue'!$AA$12:$AQ$53)))+
SUM(IF('Revenue'!$B$12:$B$53=$U26, IF('Revenue'!$AA$7:$AQ$7=F$8, 'Revenue'!$AA$12:$AQ$53)))
Bookmarks