Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 16th 04, 03:03 PM
Stan
 
Posts: n/a
Default 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

  #2   Report Post  
Old December 16th 04, 03:36 PM
Frank Kabel
 
Posts: n/a
Default

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



  #3   Report Post  
Old December 16th 04, 04:09 PM
Stan
 
Posts: n/a
Default

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




  #4   Report Post  
Old December 16th 04, 04:35 PM
Frank Kabel
 
Posts: n/a
Default

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






  #5   Report Post  
Old December 16th 04, 04:53 PM
Stan
 
Posts: n/a
Default

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








  #6   Report Post  
Old December 16th 04, 10:12 PM
Debra Dalgleish
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing a range name as an argument to the Index Function Michael Sharpe Excel Discussion (Misc queries) 3 September 5th 12 01:33 PM
changing the value of each cell in a range by a certain percentage Aaron Excel Discussion (Misc queries) 5 December 15th 04 10:30 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 10:15 PM
Freezing a cell range JMorrell Excel Discussion (Misc queries) 3 December 3rd 04 06:09 PM
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 1 December 2nd 04 08:30 PM


All times are GMT +1. The time now is 03:42 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017