Sum if with several criteria?

[koda86]

Hi,
I would like to know if it is possible to choose several criteria in this formula, for example:
SUMIF('Global Sheet'!F5:F5502,"<"&'Research per date'!E20,'Global Sheet'!K5:K5502)
I would like to add a new criteria after "<"&'Research per date'!E20


Thank you

[Carim]

Hi,

Since Sumif() can only accept one condition, you have to use the function
sumproduct() which accepts multiple conditions ...

Please post a zipped copy of your worksheet, for a precise answer ...

HTH
Carim

[oldchippy]

Try this,

SUMIF('Global Sheet'!F5:F5502,"<"&'Research per date'!E20,'Global Sheet'!K5:K5502)+SUMIF(Your Range,"<"&'Research per date'!E20,Your Range)

[koda86]

Thank you for your answer but the last one did not work so I am trying to see with Sumproduct if i can have 2 criterias.

Thanks again

[Carim]

My suggestion, if you do not want to upload your worksheet, is to search in the forum for the many concrete examples of how to use sumproduct() ...

HTH
Carim

[koda86]

Hi,
I tried with SUM Product but i think there was a misunderstanding i would like to do for different ranges 2 conditions: for example,
SUMIF(A1:A5 <3 + B10:B23 = "a", K50:K500) the sum of K50:K500 if A1:A5 inf to 3 and B10:B23 = "A".

Is this possible?

Thank you again for your help

[Carim]

Please post a zipped copy of your worksheet, for a precise answer ...

HTH
Carim

[koda86]

Hi,
I have posted the worsheet with this message so it is clearer for you: in fact I would like to calculate the number of absences and their cots presents in the chart at the top. So that for example when they select in the drop list" CPS" they can see the number of absences and their cost for CPS before the date written above in the cell E12.
My 2 criteria are the department and before the date above.


Thank you if you can help me!

[Carim]

Hi,

Thanks for the zipped file ...
When you mention between two dates ...
only the end date is shown ... ???

Carim

[koda86]

Of course there is only the end date so it is before the end date.

Thank you

[Carim]

Hi,

Attached is your worksheet with the sumproduct() formula ...

HTH
Carim

[koda86]

Thank you for your quick answer, it is perfect but just to know if my information are in another sheet how can I write it?

=SUMPRODUCT((Global Sheet!C5:C5000=D4)*(Global Sheet!F5:F5000<=E20)) because like this it does not work!

Thanks

[Carim]

Adjust formula as follows ...

Please Login or Register  to view this content.

'Global Sheet'! ...

HTH
Carim

[koda86]

Thank you for your help and your patience!

[Carim]

Glad your problem is fixed.

Thanks for the feedback

Carim