SUMIFS with ISNUMBER(SEARCH

azad092

Board Regular
Joined
Dec 31, 2019
Messages
198
Office Version
  1. 2007
Platform
  1. Windows
Dear Members
to calculate a specific value I want to use the following formula
=SUMPRODUCT(--((ISNUMBER(SEARCH("MA-02",MTN!M2:M90000))))) its working perfectly
but I want to do this with tow conditions ie with this part of coding
,MTN!M2:M90000,MTN!$B$2:$B$90000,"="&'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
and I also change the SUMPRODUCT into SUMIFS
the formula change into this shape but its not working and giving an error

=SUMIFS(--((ISNUMBER(SEARCH("MA-02",MTN!M2:M90000,MTN!$B$2:$B$90000,"="&'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
If someone knows about this please help me
thanks
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Why SUMIFS? It is better if you tell us what you want to do as well as how you're trying to do it. Your formula looks like you're trying to count, not sum, which means that you need COUNTIFS.

I think that this is what you're trying to do.

=COUNTIFS(MTN!$M$2:$M$90000,"*MA-02*",MTN!$B$2:$B$90000,'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
 
Upvote 0
hi jasonb
your idea is great, you understand my issue very clearly and resolved it very well
thanks alot
 
Upvote 0
Why SUMIFS? It is better if you tell us what you want to do as well as how you're trying to do it. Your formula looks like you're trying to count, not sum, which means that you need COUNTIFS.

I think that this is what you're trying to do.

=COUNTIFS(MTN!$M$2:$M$90000,"*MA-02*",MTN!$B$2:$B$90000,'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
hi jasnob
I have applied your suggested coding but I found an issue
actually this part of coding(,MTN!$B$2:$B$90000,'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3) is a range of date i.e I enter the date in column B and in G Report column Z is a start date and AA is a end date
the formula match the start and end date of Column B and then calculate values.
but issue is that it works for one date
for example if I make 10 entries of date 22/7/20 and 5 entries of date 23/7/20
and I enter date in column Z 22/7/20 and AA as 23/7/20, it calculates only date 22, it not includes the date 23
=COUNTIFS(MTN!$G$2:$G$90000,"RTA",MTN!$B$2:$B$90000,"="&$Z$3,MTN!$B$2:$B$90000,"<="&$AA$3)
Date Start Date End Date
 
Upvote 0
I thought that the criteria in your formula looked wrong, but as you hadn't told us what it should be doing, I only corrected the syntax of the part that would have been causing the error.

The very slight change below (in red) should fix it.

=COUNTIFS(MTN!$G$2:$G$90000,"RTA",MTN!$B$2:$B$90000,">="&$Z$3,MTN!$B$2:$B$90000,"<="&$AA$3)
 
Upvote 0
I thought that the criteria in your formula looked wrong, but as you hadn't told us what it should be doing, I only corrected the syntax of the part that would have been causing the error.

The very slight change below (in red) should fix it.

=COUNTIFS(MTN!$G$2:$G$90000,"RTA",MTN!$B$2:$B$90000,">="&$Z$3,MTN!$B$2:$B$90000,"<="&$AA$3)
Hi jasnob
good morning
actually I was confused about the mathematical operators, but your guidelines are useful and valueable
thanks dear
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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
Back
Top