SUMIF with only one criteria cell within range
I need to use SUMIF to calculate a subtotal of column B based on the criteria
of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan |
Hi
it would be much easier if you could fill column A completely with the group name. Could you change this layout? -- Regards Frank Kabel Frankfurt, Germany "Stan" schrieb im Newsbeitrag ... I need to use SUMIF to calculate a subtotal of column B based on the criteria of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan |
Frank,
Unfortunately, the downloaded schedule is coming from other division, straight from the G/L. Besides, we usually have over 1500 rows of data. I was hoping to find a formula (not a macro) instead of fill-in blanks cells in Col A. Stan "Frank Kabel" wrote: Hi it would be much easier if you could fill column A completely with the group name. Could you change this layout? -- Regards Frank Kabel Frankfurt, Germany "Stan" schrieb im Newsbeitrag ... I need to use SUMIF to calculate a subtotal of column B based on the criteria of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan |
Hi
if you like email me a sample file and I'll create some formulas for this -- Regards Frank Kabel Frankfurt, Germany "Stan" schrieb im Newsbeitrag ... Frank, Unfortunately, the downloaded schedule is coming from other division, straight from the G/L. Besides, we usually have over 1500 rows of data. I was hoping to find a formula (not a macro) instead of fill-in blanks cells in Col A. Stan "Frank Kabel" wrote: Hi it would be much easier if you could fill column A completely with the group name. Could you change this layout? -- Regards Frank Kabel Frankfurt, Germany "Stan" schrieb im Newsbeitrag ... I need to use SUMIF to calculate a subtotal of column B based on the criteria of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan |
Thanks, Frank.
Maybe I'm missing something, but can I have your email address? Stan "Frank Kabel" wrote: Hi if you like email me a sample file and I'll create some formulas for this -- Regards Frank Kabel Frankfurt, Germany "Stan" schrieb im Newsbeitrag ... Frank, Unfortunately, the downloaded schedule is coming from other division, straight from the G/L. Besides, we usually have over 1500 rows of data. I was hoping to find a formula (not a macro) instead of fill-in blanks cells in Col A. Stan "Frank Kabel" wrote: Hi it would be much easier if you could fill column A completely with the group name. Could you change this layout? -- Regards Frank Kabel Frankfurt, Germany "Stan" schrieb im Newsbeitrag ... I need to use SUMIF to calculate a subtotal of column B based on the criteria of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan |
There are some quick techniques for filling blank cells he
http://www.contextures.com/xlDataEntry02.html Or, add a column, and calculate the group there. For example, in cell C1, enter: =IF(A2<"",A2,C1) Copy the formula down to the last row of data. Use the new column in your subtotal formula. Stan wrote: I need to use SUMIF to calculate a subtotal of column B based on the criteria of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 04:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com