SUMPRODUCT/COUNTIFS With 3 Variables Including 1 Date

**Bellio3105** · 10-04-2012, 05:08 PM

Hi,

I'm trying to get a formula which examines three colums and counts where column A contains 'X', Column B contans 'Y' and Column C contains a date in July.

I know this may be hard to comprehend, so I've attached a sample of the data with the table below which I wish to populate.

I've tried both countifs and sumproducts but don't seem to be able to get it to work correctly.

Any help would be greatly appreciated.

Thanks,

Dan

**Pete_UK** · 10-04-2012, 05:33 PM

Put this formula in G2:

Formula:

Please Login or Register  to view this content.

Format as currency with 2dp, then copy across and down to fill your table.

Hope this helps.

Pete

**Blake 7** · 10-04-2012, 05:34 PM

Ive just seen pete's response which doesnt need a helper col. Go with that!

one way.

as per your example, insert a helper column in Col a and in a2:

Please Login or Register  to view this content.

copy down then in h2

Please Login or Register  to view this content.

drag across and down .

hope this helps

**Bellio3105** · 10-04-2012, 05:41 PM

Thanks for the quick help. Formula works perfect.

**TMS** · 10-04-2012, 05:42 PM

To count:

G2: =SUMPRODUCT(--($B$2:$B$47=G$1),--(TEXT($A$2:$A$47,"mmmm")=$F2))

To sum:

G2: =SUMPRODUCT(--($B$2:$B$47=G$1),--(TEXT($A$2:$A$47,"mmmm")=$F2),($C$2:$C$47))

Regards, TMS