Sum values by date range in multiple columns

[trimiii]

Hi,

I need help, I am using this formula =SUMPRODUCT(--(R6:R29>=D39), --(R6:R29<=D40), --(B6:B29=B41)) which counts my dates only in one column, but I need to expand to provide the result for more than 3 columns.
As you can see this formula is only for column R, how do I include mroeo column such R,S,T... Z?

thanks.

 

[Peter_SSs]

That's correct the August for Ford was 3

Why is it 3 when in that post 3 image, using the column headings and numbers on the left, P1, P2, P8 and R1 are all Ford in August?

 

[trimiii]

Hi Pet,

P2:P4 has 1 Aug for Ford only
P5:P8 has 1 Aug for BMW only.

Now when I insert the same multi values for Aug (for ford) from R2: Z8, it only counts one row...

and same thing for BMW

 

[Peter_SSs]

I don't understand your last post. Frankly, I am lost.
Suggest you start again with another sample (that I can copy & clearly identify which cells the data is in) and explain the results and exactly how you get them manually.

 

[trimiii]

Well, let me start over... please see picture attached...

I need to calculate the monthly total for Ford and BMW but for some reason, it only counts the highlighted cells but not the cells with red letters.

any help please?

 

[trimiii]

Anyway, the other formula just doesn't work for me... I think I am doing some wrong which it's ok... my fault.

However, I am using this formula that you provided and I need to add this function for more columns (like S,T... Z) and it gives me error value... any idea?

=SUMPRODUCT(--ISNUMBER(FIND(TEXT(P$11,"mmyy"),TEXT($P$2:$P$8,"mmyy")&"|"&TEXT($R$2:$R$8,"mmyy"))),--($B$2:$B$8=$B13))

 

[Peter_SSs]

I need to calculate the monthly total for Ford and BMW but for some reason, it only counts the highlighted cells but not the cells with red letters.

OK, it looks like you want to count all the cells in a row that have the correct brand in column B and a correct date in any column. Is that correct?

If so, the problem we are having stems from post 3 where you said ..

I am trying to count the value between dates in multi-columns, see picture below...

.. but your result in that picture for Ford in August was 3 when there are 4 cells that meet the above criteria. When I questioned you about that you seemed to me to be saying that 3 was correct even though the text in post 3 said multi-column. I therefore continued to assume that, although the data was multi-column, once a correct date was found, the counting on that row should stop & check the next row.

Anyway I will wait for your response to my question above in this post, and below.

What sort of data is in the hidden columns Q, S, U, etc? Is it dates? Text, Numerical, Mixture (of what?)?

 

[DanteAmor]

P24 has 1 Aug for Ford only
P58 has 1 Aug for BMW only.

Just for the record.
If you put : P or : D together, a little face will appear ( :D it's new in the forum format)

 

[Peter_SSs]

Just for the record.
If you put : P or : D together, a little face will appear ( :D it's new in the forum format)

Also for the record, Some discussion about these (& others) and possible ways to avoid it in this thread.

 

[DanteAmor]

Also for the record, Some discussion about these (& others) and possible ways to avoid it in this thread.

Thanks Pete

 

[trimiii]

OK, it looks like you want to count all the cells in a row that have the correct brand in column B and a correct date in any column. Is that correct?

If so, the problem we are having stems from post 3 where you said ..
.. but your result in that picture for Ford in August was 3 when there are 4 cells that meet the above criteria. When I questioned you about that you seemed to me to be saying that 3 was correct even though the text in post 3 said multi-column. I therefore continued to assume that, although the data was multi-column, once a correct date was found, the counting on that row should stop & check the next row.

Anyway I will wait for your response to my question above in this post, and below.

What sort of data is in the hidden columns Q, S, U, etc? Is it dates? Text, Numerical, Mixture (of what?)?

Hi Pete,

What sort of data is in the hidden columns Q, S, U, etc? Is it dates? Text, Numerical, Mixture (of what?)? - for the hidden column, I have the stats P,P-F1, P-F2 and P-F13 (status of the completed test; its mixture.

Maybe were cofusion on my questions, however, what I need is to coint me the total of the dates by month.

I hope this explain you better.