sumif or sumproduct based on current month and year

[Antispam246]

2 columns, one date format xx/xx/xxxx and the other accounting format

I'm looking to sum the value of the accounting column based on the current month and year.

Preferably without having to add additional columns.

I was following a previously used SUMPRODUCT formula but it kept giving me VALUE! error

=SUMPRODUCT(--(MONTH(V:$V)=8),--(YEAR(V:$V)=2019),AI:$AI)

The formula is pulling the data from a different workbook, so I have editted the columns to simplify for this, although I'm wondering if that's causing the issue, not because the source is incorrect as it's auto-formulated but because it's sourced from a dropbox folder on my pc

Any help appreciated

 

[Antispam246]

Column P and the text string would be “paypal credit” unsure if case matters, also its to search for that specific string of text amongst other text, hopefully makes sense. Thank you for following up again.

 

[Aladin Akyurek]

Column P and the text string would be “paypal credit” unsure if case matters, also its to search for that specific string of text amongst other text, hopefully makes sense. Thank you for following up again.

Control+shift+enter, not just enter:

=​

SUM(IF(ISNUMBER(V:V),IF((MONTH(V:V)=8)*(YEAR(V:V)=2019)*ISNUMBER(SEARCH("paypal credit",P:P)),AI:AI)))

 

[Antispam246]

Control+shift+enter, not just enter:

=​

SUM(IF(ISNUMBER(V:V),IF((MONTH(V:V)=8)*(YEAR(V:V)=2019)*ISNUMBER(SEARCH("paypal credit",P:P)),AI:AI)))

Perfect thank you.

One slight issue, really odd. I've just tested this and manually calculated the output with an example 3 times and for some reason excel is short my 0.02p. Outputted £365.50 when it should be £365.52. Set to accounting to 2 decimal places, never had this issue on calculations before (at least I hope not).

 

[Antispam246]

Sorry, couldn't see a way to edit previous post. I'm now having the same issue in one other formula, everything prior works fine, all rounded precisely to 2 decimals. Just this formula and one added after are being rounded down and up. Tried inserting ROUND to existing formulas for 2 decimal places but still same issue.

So using the formula above with the amounts in column AI as 53.53, 141.25, 17.50, 16.00, 45.00, 34.50, 57.74 results in 365.50

Actually as I'm typing this I'm getting an idea of where the issue is potentially coming from, because this formula is essentially compounding from other formulas. Some of those figures in column AI are a result of other formulas, namely a division from another column.

Going to see if I can work this out, possibly adding ROUND to the original formulas that create the figures in AI.

 

[Aladin Akyurek]

Perfect thank you.

You are welcome.

One slight issue, really odd. I've just tested this and manually calculated the output with an example 3 times and for some reason excel is short my 0.02p. Outputted £365.50 when it should be £365.52. Set to accounting to 2 decimal places, never had this issue on calculations before (at least I hope not).

Sorry, couldn't see a way to edit previous post. I'm now having the same issue in one other formula, everything prior works fine, all rounded precisely to 2 decimals. Just this formula and one added after are being rounded down and up. Tried inserting ROUND to existing formulas for 2 decimal places but still same issue.

So using the formula above with the amounts in column AI as 53.53, 141.25, 17.50, 16.00, 45.00, 34.50, 57.74 results in 365.50

Actually as I'm typing this I'm getting an idea of where the issue is potentially coming from, because this formula is essentially compounding from other formulas. Some of those figures in column AI are a result of other formulas, namely a division from another column.

Going to see if I can work this out, possibly adding ROUND to the original formulas that create the figures in AI.

Could you custom format the result cell (the formula cell) for example as:

#,##0.00_);(#,##0.00)

​