How to count number of occurrences in a table if certain criteria are met.

[VBAhelp3456]

Hi there,

I am trying to write a formula to populate a table on sheet 2. Basically I need to count the number of deliveries, pickups or on holds for that meet certain criteria ie on June 12 product A had 1 old customer order & then picked up the product, therefore the cell would populate 1. I have complete data for June 12 as an example.

Thanks

[teylyn]

Hello,

in cell D3 use

=SUMPRODUCT((Sheet1!$C$2:$E$9=Sheet2!G$2)*(Sheet1!$A$2:$A$9=Sheet2!$B3)*(Sheet1!$B$2:$B$9=Sheet2!$C3)*(Sheet1!$C$1:$E$1=Sheet2!$A3))

copy across and down.

Your dates show as month/year but the underlying values are either day 1 or day 30. You need to straighten this out and ensure that the date values are identical on both sheets.

[FDibbins]

@ teylyn, you reference Sheet2!G$2, but that cell is empty. Is that to enter a variable?

=SUMPRODUCT((Sheet1!$C$2:$E$9=Sheet2!G$2)*(Sheet1!$A$2:$A$9=Sheet2!$B3)*(Sheet1!$B$2:$B$9=Sheet2!$C3)*(Sheet1!$C$1:$E$1=Sheet2!$A3))

[teylyn]

You're right Ford. I had created my formula in a separate column set. The correct formula would be

=SUMPRODUCT((Sheet1!$C$2:$E$9=Sheet2!D$2)*(Sheet1!$A$2:$A$9=Sheet2!$B3)*(Sheet1!$B$2:$B$9=Sheet2!$C3)*(Sheet1!$C$1:$E$1=Sheet2!$A3))

2015-02-10_18-48-36.png

Thanks for picking this up.

[FDibbins]

Thanks for the update, I was scratching my head trying to figure what I had missed

@ VBAhelp3456 Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Your profile indicates 2003, but your upload suggests a version 2007 or later?

[VBAhelp3456]

Thanks everyone for your help.

I subbed in your formula but for some reason my version turns up all zeros? Could you please tell me when I'm doing wrong?

Thanks in advance

[teylyn]

Read my first post above with regards to the date values. If you don't read, I can't help.

[VBAhelp3456]

I obviously overlooked the second part of your response - No need to be high and mighty about it!!!!!

[teylyn]

This is not about high and mighty. I take a lot of care with my replies. I test every formula and often back it up with screenshots. All I ask is that you take as much care reading my posts as I put into composing them. My intention is to help, not to offend.

As you can see from the above exchange with Ford, I also make mistakes every once in a while and I'm not too proud to admit that.

So we both goofed up in this thread. Does it matter?

For me, the only thing that matters is if you got help here, be it from my posts or from someone else's.