sumif with 2 variables

**chris_steiner83** · 09-25-2008, 09:17 AM

I got the following problem:

In a spreadsheet each line is a payment, columns include various information. I want to include a formula that gives me the sum of all payments from each month and for a specific classification code (12 in total).
For example the sum of all payments with classification code "b" from January.

Any ideas how to solve this issue?
I cannot get my head around this...

Thx for the help!

**arthurbr** · 09-25-2008, 09:25 AM

Hi and welcome to the board
do a search on this forum on SUMPRODUCT
More info on this function

**firefly2k8** · 09-25-2008, 09:28 AM

Create a column which is the concatenation of the month and clasification code, then use this as your range.

eg.
A B C D Z
month class code payment jana
jan a jana 100
feb b febb 120
mar b marb 108

sumif(C:C=Z1,D:D)

To concatenate use C2=TEXT(A2,"mmm")&B2 for example.

Martin

I got the following problem:

In a spreadsheet each line is a payment, columns include various information. I want to include a formula that gives me the sum of all payments from each month and for a specific classification code (12 in total).
For example the sum of all payments with classification code "b" from January.

Any ideas how to solve this issue?
I cannot get my head around this...

Thx for the help!

**arthurbr** · 09-25-2008, 09:31 AM

Using a Pivot Table is also a possibility

**chris_steiner83** · 09-25-2008, 10:11 AM

Thx for the replies!
Tried the concatenation table and think that could work... The only problem left is that the date is entered as a number not as text. If I make a new table to concatenate date+classification there is no relation anymore to payments.

Thx again!!!

**ChemistB** · 09-25-2008, 10:58 AM

Here's an example where I created a table using the SUMPRODUCT function. Let us know if this works for you.

Please Login or Register  to view this content.

where your dates are in columnA, Code in B and payment in C. Your referenced month number is in E and your referenced code is in row 2 beginning with F.

**sabunabu** · 09-25-2008, 11:37 AM

A sum with 2 if statements and then ctrl-shift-enter will also work. Let me know if the above options don't work.

**chris_steiner83** · 09-26-2008, 04:32 AM

still got the problem to define my date column as criteria...
dates are shown as "jan, feb, mar..." but the criteria can´t be "jan" as the data is no text in the original field.

Thx again!

**chris_steiner83** · 09-26-2008, 05:34 AM

I attached the formula thats not working currently...

=SUMPRODUCT(--MONTH([SXSL.xls]Pagos!$G$576:$G$2500)=$C16)*([SXSL.xls]Pagos!$C$576:$C$2500=B18)*([SXSLxls]Pagos!$H$576:$H$2500)

First parameter contains dates, 2nd classification and the 3rd the payments

Can it be an issue that I use data from another worksheet?

Thx!

**arthurbr** · 09-26-2008, 05:42 AM

What error do you get ?
Can you post a small sample ?

**chris_steiner83** · 09-26-2008, 05:51 AM

"a value used in the formula is of the wrong data type"
guess its the date values that dont function...

**arthurbr** · 09-26-2008, 07:34 AM

A small sample of your data really would help....

**chris_steiner83** · 09-26-2008, 07:48 AM

Ok, the analysis worksheet got the formula in C4.
At least I get a result by now although its a false one...

Thx!!!

**arthurbr** · 09-26-2008, 08:15 AM

I adapted the underlying data from row2 on the Analysis sheet and the formula ( absolute and relative ref)

**chris_steiner83** · 09-26-2008, 08:34 AM

Thx arthurbr,

Works on the sample sheet but not on the original data...
Error: "a value is not available to the formula or function" appears.

Guess there has to be a mistake on the data side or? any other ideas?

Thx again!