Sumproduct help

Hello,

I wrote a SUMIFS at home (It took me ages to figure it out) and when I got to work it wouldnt work.

I have realised that it won't work as I am using excel 2003 at work.

I think I need to replace it with a SUMPRODUCT, but have no idea if this is possible or where to start.

Can someone tell me if this is possible and give me some direction please?

I have got a spreadsheet with each areas predictions of the number of shifts they are going to want per funding stream each week, and want to fill in a table next that what they have actually ordered.

These are the details:

I want to populate the ‘Machine Plotter’ tab from the ‘RRVs’ tab.

I want to count the total number of shifts (Z), for each week (A) for each section (G) by the type of funding. (N)

And (if possible) I only want to count them if the status (AA) is confirmed, ordered or pending.

Any help would be much appreciated!Machine TEST 2015-16.xlsxMachine TEST 2015-16.xlsx

Brilliant thanks, I will definitely take a look at the tutorial when the little one has gone to bed. It will definitely make life easier if I understand it.

And many thanks for providing a formula.

I pasted this into D4 (Where I want the results for what has been ordered) and I expected the answer to be 5 shifts for Derby OT Project Funded in week 1, however, it returns 2.

Could I be doing some wrong?

I will take a proper look at check the obvious later tonight!

Sorry for the confusion, I meant I put the formula in D4 (Burton / OPEX / 1) and dragged it all the way down to D31.

I thought I would double check a few results - D16 returns 2, however, I thought it would say 5.

The formula looks exactly the same as the one you have put in for D16

=SUMPRODUCT((RRVs!$G$2:$G$6000='Machine Plotter'!$A16)*
(RRVs!$A$2:$A$6000='Machine Plotter'!D$3)*
(RRVs!$N$2:$N$6000='Machine Plotter'!$B16)*
(((ISNUMBER(SEARCH("CONFIRMED",RRVs!$AA$2:$AA$6000)))+(ISNUMBER(SEARCH("ORDERED",RRVs!$AA$2:$AA$6000)))+
(ISNUMBER(SEARCH("PENDING",RRVs!$AA$2:$AA$6000))))>0)*
(RRVs!$D$2:$D$6000))

Am I doing something wrong?

Thanks, I didnt mean exactly the same in each cell. I meant the equivalent formula you posted for D16, is the same formula I got in D16, (from copying/dragging the formula from D4 to D31) which returned 2.

Did you get 5 or 2?

Is the formula counting the number of occurrences or is it totalling the number of shifts?

Derby OT / Project / 1 - occurs twice for week 1 with a total of 5 shifts.

I may have not explained what I wanted clearly enough. Sorry for the confusion if I have asked you incorrectly.

Brilliant - That works Thanks!!!

Your help is much appreciated.

It's so hard to explain exactly what you want over the internet, its hardly surprising it sometimes gets lost in translation.

I will definitely look at your tutorial later.

Thanks again for your help!