SUMIF function for 2 criteria

Hi all, good afternoon.
This is my table

A1 A2 A3 A4
1 Com Non C NA Date
2 76 20 30 04-Apr
3 126 05-Apr
4 126 06-Apr


The column A1 is where I am doing the calculation.
I want the calculations to be done in A1. A1 should be 126 minus the sum of A2 and A3 HOWEVER
I do NOT want the 126 to appear.

I tried SUMIF(A2:a3,">1", 126-(A2+a3)) but this returns back error
Is there a way to do this without having the 126 in that cell..?

Thanks

Welcome to the forum stoncul,

Unfortunately, I can't work out what you are trying to achieve.

The table does not fit your words.

1 You have A1, A2, A3 A4 in your first row. Do you mean A B C D - are these Column Headings?

2. The next row (1) has Com in the first position. This would be Cell A1. Therefore, I assume that you want to do something with 76, 126, and 126, but I can't work out what!!! Where do you want to do this calculation?

You need to post a workbook so that your needs are clearer.

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David A Coop,
I've attached the simple worksheet.


2. The next row (1) has Com in the first position. This would be Cell A1. Therefore, I assume that you want to do something with 76, 126, and 126, but I can't work out what!!! Where do you want to do this calculation? Cell A1 is 76. Com is actually the column heading

Olly you are absolutely right!

On a side note, is there a reference to which I can understand the code. I could have never figured that out

Hi stonecut,

I see Olly gave you the answer before I got back to the site!

Olly's formula is using two functions - IF and AND. It is effectively saying if the cell in column B AND the cell in column C are empty (""), then the answer is empty. Otherwise, it is 126 minus whatever is in column B and C on this same row.

I like Olly's suggestion in his earlier post to use a named variable MyTarget. It means that you can just change ONE cell if that target changes instead of multiple cells.

I hope this helps, please let me know!

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".