Complex Sumif

  • Thread starter Thread starter El Bee
  • Start date Start date
E

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
 
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)
 
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
 
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.
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top