MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorry this was posted yesterday but was not here today. Sumif question


Posted by chief on January 30, 2002 12:51 PM

=SUMIF('Dispatch Record Sheet 1'!$B$6:$B$57,$H5,'Dispatch Record Sheet 1'!$D$4)
=SUMIF('Dispatch Record Sheet 1'!$C$6:$C$57,$A5,'Dispatch Record Sheet 1'!$D$4)

I have two formulas here and both work, what I need to do is join them were if both are true than it sums D4 cell.


Posted by Aladin Akyurek on January 30, 2002 1:05 PM

Yep. Now invisible or lost along with my reply.

I believe I suugested something like:

=IF(AND(COUNTIF('Dispatch Record Sheet 1'!$B$6:$B$57,$H5)>0,COUNTIF('Dispatch Record Sheet 1'!$C$6:$C$57,$A5)>0),'Dispatch Record Sheet 1'!$D$4,0)

Aladin

============

Posted by Chief on January 30, 2002 1:19 PM


This works for one incident, I don't know how it was doing it but with one of the formulas before it would go down Dipatch Record Sheet 1 and get the sum of d4, d7, d10, and so on to d55.
Do you know how I could add this to the formula?

Posted by Aladin Akyurek on January 30, 2002 1:38 PM

> This works for one incident, I don't know how it was doing it but with one of the formulas before it would go down Dipatch Record Sheet 1 and get the sum of d4, d7, d10, and so on to d55.

I took your own SUMIF formulas literally, whose 3rd arguments mention just one cell, not a range. So, new info changes the things:

=SUMPRODUCT(('Dispatch Record Sheet 1'!$B$4:$B$57=$H5)+0,'Dispatch Record Sheet 1'!$C$4:$C$57=$A5)+0,'Dispatch Record Sheet 1'!$D$4:$D$57)

Watch out for range specifications. The ranges must be of equal length.

This formula computes by ANDing.

If this is not what you want, describe more in detail what you want to achieve.

Aladin

========


Posted by Chief on January 30, 2002 1:59 PM


This is hard to explain. I could send you the workbook I am working on.
The entry in d4 is tons of salt.
The entry in d7 is tons of salt and so on.
I want a formula on my other sheet that would only go and get the salt if the truck and the employee is the same as I put it on a tally sheet.
Right now I can only get it to work if I put a truck, the trouble I'm getting into is when I try and write a formula to only get the salt if both matches. There is occasions when a different employee is driving the same truck later in the day.


Posted by Chief on January 30, 2002 2:05 PM

Put wrong address


Posted by Aladin Akyurek on January 30, 2002 2:14 PM

YOUR mail address does not work. The entry in d4 is tons of salt.