forumla that counts total per month

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239
I've tried and tried . but don't seem to be able to get the right formula or even workout which function i should thorougly investigate.

please please please tell me how to sort this out.

Through the help on this site i now can produce a report that sums the total once three variables are met.

So far i have:
column A: London, Paris, Tokyo.
column B: three different types of fruit (for simplicities sake ) apple, pear, banana.
column C: will say whether it has been "eaten" or "not eaten".

I have the formula which sums the possible outcomes. so for the three cities i'd like to see how many apples have been eaten and how many not eaten and the same for the pears and bananas.

so, now i'd like to add another column D for date. so i can sum all the data for eaten, not eaten in a particular month like feb and march. any ideas on how to achieve this?

the original formula given by the very helpful phantom (thanks mate) was: =SUMPRODUCT(($A$1:$A$17="London")*($B$1:$B$17="apple")*($C$1:$C$17="eaten"))

the original answer was here:
http://www.mrexcel.com/board2/viewtopic.php?t=127441&highlight=

please can anyone give me teh formula that will achieve this??

as always, thanks very much in advance for your help

yours

john
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Try:

=SUMPRODUCT(($A$1:$A$17="London")*($B$1:$B$17="apple")*($C$1:$C$17="eaten")*(month($C$1:$C$17)=2))

for Feb.
 

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239
Fantastic Oaktree....I love you !

formula works perfectly.

thanks for your help

yours

john
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top