Complex Sumif

[El Bee]

I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee

 

[T. Valko]

Will Sumif work here?

No.

Column J = Month of year

Is that the month name as a TEXT string like June?

Try this:

Use cells to hold your criteria:

A1 = cost center
B1 = month name as a TEXT string = June

=SUMPRODUCT(--(C1:C100=A1),--(J1:J100=B1),G1:G100)

 

[FSt1]

hi
sumif is good only for 1 criteria. sumproduct would do the same and works
with more than one criterial.
a example based on your example.....
=SUMPRODUCT((C2:C100="cost center")*(G2:G100)*(J2:J100="march"))

if you have never worked with sumproduct, see this site for more info
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

regards
FSt1

 

[El Bee]

I found this formula to work, sometimes!
In my spreadsheet I have 4 Names: Cost_Center, Acct_nbr, Mth, and Amt.

These names point to different columns on a seperat worksheet. I used the
forumla help menu option to build a Sumproduct array and with each array I
entered it would display the resuts; correctly but when I closed the window
and the formula entered into the cell I get the #N/A error.

Here's the formula
=SUMPRODUCT((Acct=660000)*(Cost_Center="1170-17008")*(Mth="Mar"),Amt)
There is 2 records and it has the following values:
Record #1:
Acct= 660000
Cost_Center = "1170-17008"
Mth = "Mar"
Amt = $20.75

Record #2:
Acct= 660000
Cost_Center = "1170-17008"
Mth = "Apr"
Amt = $750.00

According to the formula I should see a value of $20.75 but I get #N/A
instead.

Not sure why this is happening

 

[El Bee]

UPDATE:

I discovered that one of my Name Definitions did not contain the same number
of rows as the rest of them. I fixed this problem and now I get #VALUE in
the cell.

Nothing else has changed.

 

[El Bee]

Stop The Presses!!

It now works; turns out there was another names definition that was missing
the first cell in the array which caused the error.

Thanks for the initial help and the web site. It's been a slow learning
process.

 

[T. Valko]

Good deal. Thanks for the feedback!